--8.3 更新数据
use tempdb;
go

if OBJECT_ID('dbo.orderdetails', 'u') is not null drop table dbo.orderdetails
if OBJECT_ID('dbo.orders', 'u') is not null drop table dbo.orders

select * 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.5
where productid = 51

update dbo.orderdetails
set discount += 0.5
where productid = 51

--在SQL中所有的赋值表达式好像都是同时进行计算的

--8.3.2 基于联接的Update
update od
set discount += 0.5
from dbo.orderdetails as od
join dbo.orders as o
on od.orderid = o.orderid
where custid = 1;

update dbo.orderdetails
set discount = discount + 0.5
where exists
(select * from dbo.orders 
where dbo.orders.orderid = dbo.orderdetails.orderid
and dbo.orders.custid = 1);

--在某些情况下,使用联接比使用子查询在性能上更具优势.除了过滤作用,通过联接
--还可以访问其他表的属性(列)，并在SET子句中使用这些属性为列属性。

update T1
set T1.COL1 = T2.COL1,
T1.COL2 = T2.COL2,
T1.COL3 = T2.COL3
from dbo.T1 join dbo.T2
on T2.keycol = T1.keycol
where 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;
go

create 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;
go

create 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 TGT
using dbo.customersstage as SRC
on TGT.custid = SRC.custid
when matched then
update set
tgt.companyname=src.companyname,
tgt.phone=src.phone,
tgt.address=src.address
when not matched then
insert (custid, companyname, phone, address)
values(src.custid, src.companyname, src.phone, src.address);

select * from dbo.customers