SQL Server/T-SQL Tutorial/Insert Delete Update/Update

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

An UPDATE statement that assigns the maximum due date in the table to a specific Billing

   <source lang="sql">

3> 4> 5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 4> GO (1 rows affected) 1> 2> 3> 4> UPDATE Billings 5> SET CreditTotal = CreditTotal + 100, 6> BillingDueDate = (SELECT MAX(BillingDueDate) FROM Billings) 7> WHERE BillingNumber = "2" 8> GO (1 rows affected) 1> 2> 3> 4> drop table Billings; 5> GO</source>


An UPDATE statement that changes the terms of all Billings for Bankers in three states

   <source lang="sql">

6> 7> create table Billings ( 8> BankerID INTEGER, 9> BillingNumber INTEGER, 10> BillingDate datetime, 11> BillingTotal INTEGER, 12> TermsID INTEGER, 13> BillingDueDate datetime , 14> PaymentTotal INTEGER, 15> CreditTotal INTEGER 16> 17> ); 18> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> 4> create table Bankers( 5> BankerID Integer, 6> BankerName VARCHAR(20), 7> BankerContactLName VARCHAR(20), 8> BankerContactFName VARCHAR(20), 9> BankerCity VARCHAR(20), 10> BankerState VARCHAR(20), 11> BankerZipCode VARCHAR(20), 12> BankerPhone VARCHAR(20) 13> ) 14> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> 4> UPDATE Billings 5> SET TermsID = 1 6> WHERE BankerID IN 7> (SELECT BankerID 8> FROM Bankers 9> WHERE BankerState IN ("CA", "AZ", "NV")) 10> 11> GO (1 rows affected) 1> 2> drop table Bankers; 3> drop table Billings; 4> GO</source>


An UPDATE statement that updates all the Billings for a Banker based on the Banker"s name

   <source lang="sql">

5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> 4> create table Bankers( 5> BankerID Integer, 6> BankerName VARCHAR(20), 7> BankerContactLName VARCHAR(20), 8> BankerContactFName VARCHAR(20), 9> BankerCity VARCHAR(20), 10> BankerState VARCHAR(20), 11> BankerZipCode VARCHAR(20), 12> BankerPhone VARCHAR(20) 13> ) 14> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> 4> UPDATE Billings 5> SET TermsID = 1 6> WHERE BankerID = 7> (SELECT BankerID 8> FROM Bankers 9> WHERE BankerName = "Pacific Bell") 10> GO (0 rows affected) 1> 2> drop table Bankers; 3> drop table Billings; 4> GO</source>


An UPDATE statement that uses an arithmetic expression to assign a value to a column

   <source lang="sql">

3> 4> 5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 4> GO (1 rows affected) 1> 2> 3> 4> 5> UPDATE Billings 6> SET CreditTotal = CreditTotal + 100 7> WHERE BillingNumber = "2" 8> GO (1 rows affected) 1> 2> 3> drop table Billings; 4> GO</source>


An UPDATE statement with top

   <source lang="sql">

6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> UPDATE Billings 3> SET CreditTotal = CreditTotal + 100 4> FROM 5> (SELECT TOP 10 BankerID 6> FROM Billings 7> WHERE BillingTotal - PaymentTotal - CreditTotal >= 100 8> ORDER BY BillingTotal - PaymentTotal - CreditTotal DESC) AS TopBillings 9> WHERE Billings.BankerID = TopBillings.BankerID 10> 11> GO (0 rows affected) 1> 2> 3> drop table Billings; 4> GO</source>


Assigning Update Values Using Subqueries

   <source lang="sql">

7> CREATE TABLE sales( 8> stor_id char(4) NOT NULL, 9> ord_num varchar(20) NOT NULL, 10> ord_date datetime NOT NULL, 11> qty smallint NOT NULL, 12> payterms varchar(12) NOT NULL, 13> title_id varchar(80) 14> ) 15> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE titleauthor( 4> au_id varchar(20), 5> title_id varchar(20), 6> au_ord tinyint NULL, 7> royaltyper int NULL 8> ) 9> GO 1> 2> insert titleauthor values("1", "2", 1, 60) 3> insert titleauthor values("2", "3", 1, 100) 4> insert titleauthor values("3", "4", 1, 100) 5> insert titleauthor values("4", "5", 1, 100) 6> insert titleauthor values("5", "6", 1, 100) 7> insert titleauthor values("6", "7", 2, 40) 8> insert titleauthor values("7", "8", 1, 100) 9> insert titleauthor values("8", "9", 1, 100) 10> 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> UPDATE titleauthor 2> SET royaltyper = (SELECT SUM(qty) 3> FROM sales 4> WHERE sales.title_id = titleauthor.title_id) 5> GO (8 rows affected) 1> 2> 3> drop table sales; 4> drop table titleauthor; 5> GO</source>


Change the phone number of authors living in Gary, IN, back to the DEFAULT value

   <source lang="sql">

4> CREATE TABLE authors( 5> au_id varchar(11), 6> au_lname varchar(40) NOT NULL, 7> au_fname varchar(20) NOT NULL, 8> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 9> address varchar(40) NULL, 10> city varchar(20) NULL, 11> state char(2) NULL, 12> zip char(5) NULL, 13> contract bit NOT NULL 14> ) 15> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> 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> 2> 3> 4> 5> UPDATE authors 6> SET phone=DEFAULT 7> WHERE city="Gary" AND state="IN" 8> GO (0 rows affected) 1> 2> drop table authors; 3> GO 1></source>


Limiting Rows to Be Updated

   <source lang="sql">

4> 5> 6> CREATE TABLE sales( 7> stor_id char(4) NOT NULL, 8> ord_num varchar(20) NOT NULL, 9> ord_date datetime NOT NULL, 10> qty smallint NOT NULL, 11> payterms varchar(12) NOT NULL, 12> title_id varchar(80) 13> ) 14> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE titleauthor( 4> au_id varchar(20), 5> title_id varchar(20), 6> au_ord tinyint NULL, 7> royaltyper int NULL 8> ) 9> GO 1> 2> insert titleauthor values("1", "2", 1, 60) 3> insert titleauthor values("2", "3", 1, 100) 4> insert titleauthor values("3", "4", 1, 100) 5> insert titleauthor values("4", "5", 1, 100) 6> insert titleauthor values("5", "6", 1, 100) 7> insert titleauthor values("6", "7", 2, 40) 8> insert titleauthor values("7", "8", 1, 100) 9> insert titleauthor values("8", "9", 1, 100) 10> 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> 2> 3> UPDATE titleauthor 4> SET royaltyper = royaltyper * 1.1 5> WHERE title_id IN 6> (SELECT title_id 7> FROM sales 8> GROUP BY title_id 9> HAVING sum(qty) >=30) 10> GO (2 rows affected) 1> drop table sales; 2> drop table titleauthor; 3> GO</source>


SET other columns to their default value

   <source lang="sql">

3> CREATE TABLE update_def 4> ( 5> up_id int PRIMARY KEY, 6> up_byname varchar(30) NOT NULL DEFAULT SUSER_SNAME() 7> CHECK (up_byname=SUSER_SNAME()) 8> -- Assume other columns would be here 9> ) 10> GO 1> 2> UPDATE update_def 3> SET 6> up_byname=DEFAULT 7> WHERE up_id=1 8> GO (0 rows affected) 1> 2> drop table update_def; 3> GO</source>


Syntax of UPDATE with a Join

   <source lang="sql">

UPDATE <modified_table> 

 SET col1 = <new_value>[,
 col2 = <new_value>]

[FROM 

   <modified_table>
<join_type> JOIN
  <another_table> ON <join_condition>]

WHERE <search_condition>]</source>


The syntax of the UPDATE statement

   <source lang="sql">

UPDATE table_name SET column_name_1 = expression_1 [, column_name_2 = expression_2]... [FROM table_source [[AS] table_alias] [WHERE search_condition]</source>


Update table in a transaction

   <source lang="sql">

7> CREATE TABLE Product( 8> ProductID int NOT NULL, 9> Name nvarchar(25) NOT NULL, 10> ProductNumber nvarchar(25) , 11> Color nvarchar(15) NULL, 12> StandardCost money NOT NULL, 13> Size nvarchar(5) NULL, 14> Weight decimal(8, 2) NULL, 15> ProductLine nchar(20) NULL, 16> SellStartDate datetime NOT NULL, 17> SellEndDate datetime NULL 18> ) 19> GO 1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22"); 2> GO (1 rows affected) 1> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 2> GO (1 rows affected) 1> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 2> GO (1 rows affected) 1> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 2> GO (1 rows affected) 1> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 2> GO (1 rows affected) 1> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 2> GO (1 rows affected) 1> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 2> GO (1 rows affected) 1> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 2> GO (1 rows affected) 1> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 2> GO (1 rows affected) 1> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 2> GO (1 rows affected) 1> 2> 3> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 4> GO 1> BEGIN TRANSACTION 2> GO 1> UPDATE Product SET StandardCost = StandardCost * 1.15 2> WHERE ProductID = 1 3> GO (1 rows affected) 1> UPDATE Product SET StandardCost = StandardCost * 1.15 2> WHERE ProductID = 1 3> AND StandardCost < 1000 4> GO (1 rows affected) 1> COMMIT TRANSACTION 2> GO 1> 2> 3> drop table Product; 4> GO</source>


Updating Data Using the CASE Expression

   <source lang="sql">

6> 7> CREATE TABLE sales( 8> stor_id char(4) NOT NULL, 9> ord_num varchar(20) NOT NULL, 10> ord_date datetime NOT NULL, 11> qty smallint NOT NULL, 12> payterms varchar(12) NOT NULL, 13> title_id varchar(80) 14> ) 15> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> UPDATE sales 4> SET payterms = 5> CASE 6> WHEN (SELECT SUM(qty) FROM sales s1 7> WHERE sales.stor_id = s1.stor_id) < 10 8> THEN "On Billing" 9> WHEN (SELECT SUM(qty) FROM sales s1 10> WHERE sales.stor_id = s1.stor_id) < 100 11> THEN "Net 30" 12> ELSE "Net 60" 13> END 14> GO (6 rows affected) 1> 2> SELECT stor_id, SUBSTRING(ord_num,1,5) ord_num, 3> ord_date, qty, payterms, title_id FROM sales 4> GO stor_id ord_num ord_date qty payterms title_id

------- ----------------------- ------ ------------ --------------------------------------------------------------------------------

1 QA744 1994-09-13 00:00:00.000 75 Net 30 1 2 D4482 1994-09-14 00:00:00.000 10 Net 30 1 3 N9140 1994-09-14 00:00:00.000 20 Net 30 2 4 N9140 1994-09-14 00:00:00.000 25 Net 30 3 5 423LL 1994-09-14 00:00:00.000 15 Net 30 3 6 423LL 1994-09-14 00:00:00.000 10 Net 30 2 (6 rows affected) 1> 2> drop table sales; 3> GO</source>


Updating Large Value Data Type Columns with WRITE

   <source lang="sql">

31> 32> CREATE TABLE Book( 33> ChapterID int NOT NULL, 34> Chapter varchar(max) NOT NULL 35> ) 36> GO 1> 2> INSERT Book(ChapterID, Chapter) 3> VALUES(1, "chapter 1" ) 4> GO (1 rows affected) 1> 2> UPDATE Book 3> SET Chapter .WRITE (" new chapter" , NULL, NULL) 4> WHERE ChapterID = 1 5> GO (1 rows affected) 1> 2> select * from Book 3> GO ChapterID Chapter

------------------------------------------------------------------------------------------------------------ 
         1 chapter 1 new chapter

(1 rows affected) 1> 2> UPDATE Book 3> SET Chapter .WRITE("new new chapter", 1, 10) 4> WHERE ChapterID = 1 5> GO (1 rows affected) 1> 2> SELECT Chapter 3> FROM Book 4> WHERE ChapterID = 1 5> GO Chapter

cnew new chapterew chapter

(1 rows affected) 1> 2> drop table Book 3> GO</source>


Updating using two subqueries.

   <source lang="sql">

5> CREATE TABLE sales( 6> stor_id char(4) NOT NULL, 7> ord_num varchar(20) NOT NULL, 8> ord_date datetime NOT NULL, 9> qty smallint NOT NULL, 10> payterms varchar(12) NOT NULL, 11> title_id varchar(80) 12> ) 13> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE titleauthor( 4> au_id varchar(20), 5> title_id varchar(20), 6> au_ord tinyint NULL, 7> royaltyper int NULL 8> ) 9> GO 1> 2> insert titleauthor values("1", "2", 1, 60) 3> insert titleauthor values("2", "3", 1, 100) 4> insert titleauthor values("3", "4", 1, 100) 5> insert titleauthor values("4", "5", 1, 100) 6> insert titleauthor values("5", "6", 1, 100) 7> insert titleauthor values("6", "7", 2, 40) 8> insert titleauthor values("7", "8", 1, 100) 9> insert titleauthor values("8", "9", 1, 100) 10> 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> UPDATE titleauthor 2> SET royaltyper = 1.1 * (SELECT SUM(qty) 3> FROM sales 4> WHERE sales.title_id = titleauthor.title_id) 5> WHERE title_id IN 6> (SELECT title_id 7> FROM sales 8> GROUP BY title_id 9> HAVING sum(qty) >=30) 10> GO (2 rows affected) 1> 2> drop table sales; 3> drop table titleauthor; 4> GO 1></source>


Violating the constraint in an update statement

   <source lang="sql">

7> CREATE TABLE MySavings( 8> AccountNum Int NOT NULL, 9> Amount Money NOT NULL 10> ); 11> GO 1> 2> CREATE TABLE MyChecking( 3> AcountNum Int NOT NULL, 4> Amount Money NOT NULL 5> ); 6> GO 1> ALTER TABLE MyChecking ADD CONSTRAINT ckMinBalance 2> CHECK (Amount > $100.00) 3> GO 1> 2> INSERT MySavings VALUES (12345, $1000.00) 3> GO (1 rows affected) 1> 2> 3> INSERT MyChecking VALUES (12345, $1000.00) 4> GO (1 rows affected) 1> 2> 3> BEGIN TRANSACTION 4> UPDATE MyChecking SET Amount = Amount - $990.00 5> WHERE AcountNum = 12345 6> UPDATE MySavings SET Amount = Amount + $990.00 7> WHERE AccountNum = 12345 8> COMMIT TRANSACTION 9> GO Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 4 The UPDATE statement conflicted with the CHECK constraint "ckMinBalance". The conflict occurred in database "master", table "dbo.MyChecking", column "Amount". The statement has been terminated. (1 rows affected) 1> 2> drop table MySavings; 3> drop table MyChecking; 4> GO</source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Insert_Delete_Update/Update&oldid=6533»