use tempdb;
go

select * into dbo.orderdetails from TSQLFundamentals2008.Sales.OrderDetails;
select * into dbo.orders from TSQLFundamentals2008.Sales.Orders;

update od
set discount=discount+0.5
from dbo.orderdetails as od
join dbo.orders as o
on od.orderid = o.orderid
where custid = 1;

with C as 
(select custid, od.orderid,
productid, discount, discount+0.5 as newdiscount
from dbo.orderdetails as od
join dbo.orders as o
on od.orderid=o.orderid)
update c
set discount=newdiscount
where custid=1;

update d
set discount = newdiscount
from (select custid, od.orderid,
productid, discount, discount+0.5 as newdiscount
from dbo.orderdetails as od
join dbo.orders as o
on od.orderid=o.orderid
where 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 c
set 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.datacol
select lastname 
from TSQLFundamentals2008.HR.Employees
where country=N'USA'

declare @NewRows table(keycol int, datacol nvarchar(40));
insert into dbo.t1(datacol)
output inserted.keycol, inserted.datacol
into @NewRows
select lastname
from TSQLFundamentals2008.HR.Employees
where 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.orders
output deleted.orderid, deleted.orderdate
where 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.05
output
inserted.productid,
deleted.discount as olddiscount,
inserted.discount as newdiscount
where 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.15
output
inserted.productid,
deleted.unitprice as oldval,
inserted.unitprice as newval
where supplierid=1) as c
where c.oldval<20.0 and c.newval>=20.0;

select * from dbo.productsaudit;