SQL Server/T-SQL Tutorial/Insert Delete Update/Update — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:46, 26 мая 2010
Содержание
- 1 An UPDATE statement that assigns the maximum due date in the table to a specific Billing
- 2 An UPDATE statement that changes the terms of all Billings for Bankers in three states
- 3 An UPDATE statement that updates all the Billings for a Banker based on the Banker"s name
- 4 An UPDATE statement that uses an arithmetic expression to assign a value to a column
- 5 An UPDATE statement with top
- 6 Assigning Update Values Using Subqueries
- 7 Change the phone number of authors living in Gary, IN, back to the DEFAULT value
- 8 Limiting Rows to Be Updated
- 9 SET other columns to their default value
- 10 Syntax of UPDATE with a Join
- 11 The syntax of the UPDATE statement
- 12 Update table in a transaction
- 13 Updating Data Using the CASE Expression
- 14 Updating Large Value Data Type Columns with WRITE
- 15 Updating using two subqueries.
- 16 Violating the constraint in an update statement
An UPDATE statement that assigns the maximum due date in the table to a specific Billing
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
An UPDATE statement that changes the terms of all Billings for Bankers in three states
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
An UPDATE statement that updates all the Billings for a Banker based on the Banker"s name
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
An UPDATE statement that uses an arithmetic expression to assign a value to a column
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
An UPDATE statement with top
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
Assigning Update Values Using Subqueries
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
Change the phone number of authors living in Gary, IN, back to the DEFAULT value
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>
Limiting Rows to Be Updated
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
SET other columns to their default value
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
Syntax of UPDATE with a Join
UPDATE <modified_table>
SET col1 = <new_value>[,
col2 = <new_value>]
[FROM
<modified_table>
<join_type> JOIN
<another_table> ON <join_condition>]
WHERE
<search_condition>]
The syntax of the UPDATE statement
UPDATE table_name
SET column_name_1 = expression_1 [, column_name_2 = expression_2]...
[FROM table_source [[AS] table_alias]
[WHERE search_condition]
Update table in a transaction
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
Updating Data Using the CASE Expression
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
Updating Large Value Data Type Columns with WRITE
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
Updating using two subqueries.
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>
Violating the constraint in an update statement
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