SQL Server/T-SQL/Transact SQL/Update Data
Содержание
Create procedure to update table based on parameters passed in
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> CREATE PROCEDURE [sp_Update_Salary](@Id [int])
3> AS UPDATE [Employee]
4> SET [Salary] = 1000
5> WHERE([Id] = @Id)
6> GO
1>
2> EXEC sp_Update_Salary @ID = 3
3> GO
(1 rows affected)
1>
2> SELECT * FROM Employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 1000 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2>
3> drop table employee
4> GO
1>
Do calculation in update statement in a procedure
33> CREATE TABLE project (project_no CHAR(4) NOT NULL,
34> project_name CHAR(15) NOT NULL,
35> budget FLOAT NULL)
36>
37> insert into project values ("p1", "Search Engine", 120000.00)
38> insert into project values ("p2", "Programming", 95000.00)
39> insert into project values ("p3", "SQL", 186500.00)
40>
41> select * from project
42> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
project_no project_name budget
---------- --------------- ------------------------
p1 Search Engine 120000
p2 Programming 95000
p3 SQL 186500
(3 rows affected)
1>
2> CREATE PROCEDURE increase_budget (@percent INT=5) AS
3> UPDATE project
4> SET budget = budget + budget*@percent/100
5> GO
1>
2> EXECUTE increase_budget 10
3> GO
(3 rows affected)
1> select * from project
2> GO
project_no project_name budget
---------- --------------- ------------------------
p1 Search Engine 132000
p2 Programming 104500
p3 SQL 205150
(3 rows affected)
1> drop table project
2> GO
1>
Update data and table join
1> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
2> project_no CHAR(4) NOT NULL,
3> job CHAR (15) NULL,
4> enter_date DATETIME NULL)
5>
6> insert into works_on values (1, "p1", "analyst", "1997.10.1")
7> insert into works_on values (1, "p3", "manager", "1999.1.1")
8> insert into works_on values (2, "p2", "clerk", "1998.2.15")
9> insert into works_on values (2, "p2", NULL, "1998.6.1")
10> insert into works_on values (3, "p2", NULL, "1997.12.15")
11> insert into works_on values (4, "p3", "analyst", "1998.10.15")
12> insert into works_on values (5, "p1", "manager", "1998.4.15")
13> insert into works_on values (6, "p1", NULL, "1998.8.1")
14> insert into works_on values (7, "p2", "clerk", "1999.2.1")
15> insert into works_on values (8, "p3", "clerk", "1997.11.15")
16> insert into works_on values (7, "p1", "clerk", "1998.1.4")
17>
18> select * from works_on
19> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
emp_no project_no job enter_date
----------- ---------- --------------- -----------------------
1 p1 analyst 1997-10-01 00:00:00.000
1 p3 manager 1999-01-01 00:00:00.000
2 p2 clerk 1998-02-15 00:00:00.000
2 p2 NULL 1998-06-01 00:00:00.000
3 p2 NULL 1997-12-15 00:00:00.000
4 p3 analyst 1998-10-15 00:00:00.000
5 p1 manager 1998-04-15 00:00:00.000
6 p1 NULL 1998-08-01 00:00:00.000
7 p2 clerk 1999-02-01 00:00:00.000
8 p3 clerk 1997-11-15 00:00:00.000
7 p1 clerk 1998-01-04 00:00:00.000
(11 rows affected)
1>
2> CREATE TABLE employee (emp_no INTEGER NOT NULL,
3> emp_fname CHAR(20) NOT NULL,
4> emp_lname CHAR(20) NOT NULL,
5> dept_no CHAR(4) NULL)
6>
7> insert into employee values(1, "Matthew", "Smith", "d3")
8> insert into employee values(2, "Ann", "Jones", "d3")
9> insert into employee values(3, "John", "Barrimore","d1")
10> insert into employee values(4, "James", "James", "d2")
11> insert into employee values(5, "Elsa", "Bertoni", "d2")
12> insert into employee values(6, "Elke", "Hansel", "d2")
13> insert into employee values(7, "Sybill", "Moser", "d1")
14>
15> select * from employee
16> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
emp_no emp_fname emp_lname dept_no
----------- -------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected)
1>
2>
3> UPDATE works_on SET job = NULL FROM works_on, employee
4> WHERE emp_lname = "Jones" AND works_on.emp_no = employee.emp_no
5> GO
(2 rows affected)
1> select * from works_on
2> GO
emp_no project_no job enter_date
----------- ---------- --------------- -----------------------
1 p1 analyst 1997-10-01 00:00:00.000
1 p3 manager 1999-01-01 00:00:00.000
2 p2 NULL 1998-02-15 00:00:00.000
2 p2 NULL 1998-06-01 00:00:00.000
3 p2 NULL 1997-12-15 00:00:00.000
4 p3 analyst 1998-10-15 00:00:00.000
5 p1 manager 1998-04-15 00:00:00.000
6 p1 NULL 1998-08-01 00:00:00.000
7 p2 clerk 1999-02-01 00:00:00.000
8 p3 clerk 1997-11-15 00:00:00.000
7 p1 clerk 1998-01-04 00:00:00.000
(11 rows affected)
1> drop table works_on
2> drop table employee
3> GO
1>
Update data set returning from the function
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> CREATE FUNCTION dbo.getData(@t AS nvarchar(50))
3> RETURNS table
4> AS
5> RETURN ( SELECT ID, Name, salary FROM Employee WHERE city = @t)
6> GO
1>
2>
3> DECLARE @t as nvarchar(50)
4>
5> SET @t = "New York"
6> SELECT * FROM dbo.getData(@t)
7>
8> SET @t = "Vancouver"
9> SELECT * FROM getData(@t)
10>
11> SET @t = "Vancouver"
12> UPDATE dbo.getData(@t)
13> SET Name = "James"
14> WHERE ID = 1
15>
16> SELECT * FROM dbo.getData(@t)
17> GO
ID Name salary
----------- ---------- -----------
1 Jason 40420
4 Linda 40620
7 Alison 90620
(3 rows affected)
ID Name salary
----------- ---------- -----------
2 Robert 14420
5 David 80026
8 Chris 26020
(0 rows affected)
ID Name salary
----------- ---------- -----------
2 Robert 14420
5 David 80026
8 Chris 26020
(3 rows affected)
1>
2> drop table employee
3> GO
1>
Update two tables in one procedure
32>
33> CREATE TABLE employee (emp_no INTEGER NOT NULL,
34> emp_fname CHAR(20) NOT NULL,
35> emp_lname CHAR(20) NOT NULL,
36> dept_no CHAR(4) NULL)
37>
38> insert into employee values(1, "Matthew", "Smith", "d3")
39> insert into employee values(2, "Ann", "Jones", "d3")
40> insert into employee values(3, "John", "Barrimore","d1")
41> insert into employee values(4, "James", "James", "d2")
42> insert into employee values(5, "Elsa", "Bertoni", "d2")
43> insert into employee values(6, "Elke", "Hansel", "d2")
44> insert into employee values(7, "Sybill", "Moser", "d1")
45>
46> select * from employee
47> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
emp_no emp_fname emp_lname dept_no
----------- -------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected)
1>
2> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
3> project_no CHAR(4) NOT NULL,
4> job CHAR (15) NULL,
5> enter_date DATETIME NULL)
6>
7> insert into works_on values (1, "p1", "analyst", "1997.10.1")
8> insert into works_on values (1, "p3", "manager", "1999.1.1")
9> insert into works_on values (2, "p2", "clerk", "1998.2.15")
10> insert into works_on values (2, "p2", NULL, "1998.6.1")
11> insert into works_on values (3, "p2", NULL, "1997.12.15")
12> insert into works_on values (4, "p3", "analyst", "1998.10.15")
13> insert into works_on values (5, "p1", "manager", "1998.4.15")
14> insert into works_on values (6, "p1", NULL, "1998.8.1")
15> insert into works_on values (7, "p2", "clerk", "1999.2.1")
16> insert into works_on values (8, "p3", "clerk", "1997.11.15")
17> insert into works_on values (7, "p1", "clerk", "1998.1.4")
18>
19> select * from works_on
20> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
emp_no project_no job enter_date
----------- ---------- --------------- -----------------------
1 p1 analyst 1997-10-01 00:00:00.000
1 p3 manager 1999-01-01 00:00:00.000
2 p2 clerk 1998-02-15 00:00:00.000
2 p2 NULL 1998-06-01 00:00:00.000
3 p2 NULL 1997-12-15 00:00:00.000
4 p3 analyst 1998-10-15 00:00:00.000
5 p1 manager 1998-04-15 00:00:00.000
6 p1 NULL 1998-08-01 00:00:00.000
7 p2 clerk 1999-02-01 00:00:00.000
8 p3 clerk 1997-11-15 00:00:00.000
7 p1 clerk 1998-01-04 00:00:00.000
(11 rows affected)
1>
2>
3> CREATE PROCEDURE modify_empno (@old_no INTEGER, @new_no INTEGER)
4> AS UPDATE employee SET emp_no = @new_no WHERE emp_no = @old_no
5> UPDATE works_on SET emp_no = @new_no WHERE emp_no = @old_no
6> GO
1>
2> EXEC modify_empno 1,99
3> GO
(1 rows affected)
(2 rows affected)
1>
2> select * from employee
3> select * from works_on
4> GO
emp_no emp_fname emp_lname dept_no
----------- -------------------- -------------------- -------
99 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected)
emp_no project_no job enter_date
----------- ---------- --------------- -----------------------
99 p1 analyst 1997-10-01 00:00:00.000
99 p3 manager 1999-01-01 00:00:00.000
2 p2 clerk 1998-02-15 00:00:00.000
2 p2 NULL 1998-06-01 00:00:00.000
3 p2 NULL 1997-12-15 00:00:00.000
4 p3 analyst 1998-10-15 00:00:00.000
5 p1 manager 1998-04-15 00:00:00.000
6 p1 NULL 1998-08-01 00:00:00.000
7 p2 clerk 1999-02-01 00:00:00.000
8 p3 clerk 1997-11-15 00:00:00.000
7 p1 clerk 1998-01-04 00:00:00.000
(11 rows affected)
1>
2> drop procedure modify_empno
3> drop table employee
4> drop table works_on
5>
6> GO
1>
2>