SQL Server/T-SQL/Transact SQL/Update Data

Материал из SQL эксперт
Перейти к: навигация, поиск

Create procedure to update table based on parameters passed in

   <source lang="sql">

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>

      </source>
   
  


Do calculation in update statement in a procedure

   <source lang="sql">

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>

      </source>
   
  


Update data and table join

   <source lang="sql">

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>

      </source>
   
  


Update data set returning from the function

   <source lang="sql">

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>

      </source>
   
  


Update two tables in one procedure

   <source lang="sql">

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>

      </source>