第八章 数据修改(3)
--8.3 更新数据use tempdb;goif OBJECT_ID('dbo.orderdetails', 'u') is not null drop table dbo.orderdetailsif OBJECT_ID('dbo.orders', 'u') is not null drop table dbo.ordersselect * into dbo.orders from TSQLFundamentals2008.Sales.Orders;select * into dbo.orderdetails from TSQLFundamentals2008.Sales.OrderDetails;alter table dbo.orders add constraint PK_Orders primary key(orderid)alter table dbo.orderdetails add constraint PK_Orderdetails primary key(orderid, productid),constraint FK_Orderdetails_Orders foreign key(orderid) references dbo.orders(orderid);--8.3.1 Update语句update dbo.orderdetails set discount = discount + 0.5where productid = 51update dbo.orderdetailsset discount += 0.5where productid = 51--在SQL中所有的赋值表达式好像都是同时进行计算的--8.3.2 基于联接的Updateupdate odset discount += 0.5from dbo.orderdetails as odjoin dbo.orders as oon od.orderid = o.orderidwhere custid = 1;update dbo.orderdetailsset discount = discount + 0.5where exists(select * from dbo.orders where dbo.orders.orderid = dbo.orderdetails.orderidand dbo.orders.custid = 1);--在某些情况下,使用联接比使用子查询在性能上更具优势.除了过滤作用,通过联接--还可以访问其他表的属性(列)，并在SET子句中使用这些属性为列属性。update T1set T1.COL1 = T2.COL1,T1.COL2 = T2.COL2,T1.COL3 = T2.COL3from dbo.T1 join dbo.T2on T2.keycol = T1.keycolwhere T2.COL4='ABC'--8.3.4 赋值UPDATE--TSQL支持特殊的UPDATE语法,可以在对表中的数据进行更新的同时为变量赋值--使用这种特殊的UPDATE语法是作为原子操作而进行的，因为它只须要访问一次数据。use tempdb;if OBJECT_ID('dbo.sequence','u') is not null drop table dbo.sequence;create table dbo.sequence(val int not null);insert into dbo.sequence values(10);declare @nextval as int;update sequence set @nextval = val = val+1;select @nextval--8.4 合并数据--MERGE的语句能在一条语句中根据逻辑条件对数据进行不同的修改操作(insert,update和delete).--用较少的代码就可以表达需求，提高查询性能，因为它可以更少地访问查询涉及表。use tempdb;if OBJECT_ID('dbo.customers','u') is not null drop table dbo.customers;gocreate table dbo.customers(custid int not null,companyname varchar(25) not null,phone varchar(20) not null,address varchar(50) not null,constraint PK_Customers Primary key(custid));insert into dbo.customers(custid, companyname, phone, address)values(1, 'cust 1', '(111)111-1111', 'address 1'),(2, 'cust 2', '(222)222-2222', 'address 2'),(3, 'cust 3', '(333)333-3333', 'address 3'),(4, 'cust 4', '(444)444-4444', 'address 4'),(5, 'cust 5', '(555)555-5555', 'address 5');if OBJECT_ID('dbo.customersstage', 'u') is not null drop table dbo.customersstage;gocreate table dbo.customersstage(custid int not null,companyname varchar(25) not null,phone varchar(20) not null,address varchar(50) not null,constraint PK_customersstage Primary key(custid));insert into dbo.customersstage(custid, companyname, phone, address)values(2, 'cust 1', '(111)111-1111', 'address 1'),(3, 'cust 2', '(222)222-2222', 'address 2'),(5, 'cust 3', '(333)333-3333', 'address 3'),(6, 'cust 4', '(444)444-4444', 'address 4'),(7, 'cust 5', '(555)555-5555', 'address 5');select * from dbo.customers;select * from dbo.customersstage;merge into dbo.customers as TGTusing dbo.customersstage as SRCon TGT.custid = SRC.custidwhen matched thenupdate settgt.companyname=src.companyname,tgt.phone=src.phone,tgt.address=src.addresswhen not matched theninsert (custid, companyname, phone, address)values(src.custid, src.companyname, src.phone, src.address);select * from dbo.customers