第八章 数据修改(4)
use tempdb;goselect * into dbo.orderdetails from TSQLFundamentals2008.Sales.OrderDetails;select * into dbo.orders from TSQLFundamentals2008.Sales.Orders;update odset discount=discount+0.5from dbo.orderdetails as odjoin dbo.orders as oon od.orderid = o.orderidwhere custid = 1;with C as (select custid, od.orderid,productid, discount, discount+0.5 as newdiscountfrom dbo.orderdetails as odjoin dbo.orders as oon od.orderid=o.orderid)update cset discount=newdiscountwhere custid=1;update dset discount = newdiscountfrom (select custid, od.orderid,productid, discount, discount+0.5 as newdiscountfrom dbo.orderdetails as odjoin dbo.orders as oon od.orderid=o.orderidwhere custid=1) as d;use tempdb;if OBJECT_ID('dbo.T1', 'u') is not null drop table dbo.T1;create table dbo.t1(col1 int, col2 int);insert into dbo.t1(col1) values(10),(20),(30);select * from dbo.t1;with c as (select col1, col2, ROW_NUMBER() over(order by col1) as rownum from dbo.t1)update c set col2=rownum;select * from dbo.t1;with c as (select top(50) * from dbo.orders order by orderid)delete from c;with c as (select top(50) * from dbo.orders order by orderid desc)update cset freight=freight+10.00;--通过在修改语句中添加output子句，就可以实现从修改语句中返回数据的功能。--在output子句中，可以指定希望从修改过的行中要返回的列和表达式。use tempdb;if OBJECT_ID('dbo.t1', 'u') is not null drop table dbo.t1;create table dbo.t1(keycol int not null identity(1,1) constraint pk_t1 primary key,datacol nvarchar(40) not null);insert into dbo.t1(datacol)output inserted.keycol, inserted.datacolselect lastname from TSQLFundamentals2008.HR.Employeeswhere country=N'USA'declare @NewRows table(keycol int, datacol nvarchar(40));insert into dbo.t1(datacol)output inserted.keycol, inserted.datacolinto @NewRowsselect lastnamefrom TSQLFundamentals2008.HR.Employeeswhere country=N'UK';select * from @newrows;use tempdb;if OBJECT_ID('dbo.orders','u') is not null drop table dbo.orders;select * into dbo.orders from TSQLFundamentals2008.Sales.Orders;delete from dbo.ordersoutput deleted.orderid, deleted.orderdatewhere orderdate<'20080101';use tempdb;if OBJECT_ID('dbo.orderdetails','u') is not null drop table dbo.orderdetails;select * into dbo.orderdetails from TSQLFundamentals2008.Sales.OrderDetails;update dbo.orderdetails set discount = discount+0.05outputinserted.productid,deleted.discount as olddiscount,inserted.discount as newdiscountwhere productid=51;use tempdb;if OBJECT_ID('dbo.productsaudit', 'u') is not null drop table dbo.productsaudit;if OBJECT_ID('dbo.products', 'u') is not null drop table dbo.products;select * into dbo.products from TSQLFundamentals2008.Production.Products;create table dbo.productsaudit(LSN int not null identity primary key,TS datetime not null default(current_timestamp),productid int not null,oldval sql_variant not null,newval sql_variant not null);insert into dbo.productsaudit(productid, oldval, newval) select * from (update dbo.products set unitprice*=1.15outputinserted.productid,deleted.unitprice as oldval,inserted.unitprice as newvalwhere supplierid=1) as cwhere c.oldval<20.0 and c.newval>=20.0;select * from dbo.productsaudit;