联查更新字段

[topkof]

表book_delivery_order 字段 invoice_no , order_sn
表ex 字段 invoice_no , order_sn
想把 book_delivery_order 表 order_sn 字段与 ex表 order_sn 字段相同值的book_delivery_order 表 invoice_no 字段 更新成 ex表 invoice_no值（附自己写执行有错语句）
例如
book_delivery_order表
invoice_no , order_sn
1 aaa
2 bbb
3 ccc
ex表
invoice_no , order_sn
222 bbb
333 ccc

book_delivery_order表执行结果
invoice_no , order_sn
1 aaa
222 bbb
333 ccc

update book_delivery_order set invoice_no=ex.invoice_no from ex where book_delivery_order.order_sn=ex.order_sn

[二月十六]

是这个意思吗？

--测试数据
if not object_id(N'Tempdb..#book_delivery_order') is null
	drop table #book_delivery_order
Go
Create table #book_delivery_order([invoice_no] int,[order_sn] nvarchar(23))
Insert #book_delivery_order
select 1,N'aaa' union all
select 2,N'bbb' union all
select 3,N'ccc'
GO
if not object_id(N'Tempdb..#ex') is null
	drop table #ex
Go
Create table #ex([invoice_no] int,[order_sn] nvarchar(23))
Insert #ex
select 222,N'bbb' union all
select 333,N'ccc'
Go
--测试数据结束
UPDATE  #book_delivery_order
SET     invoice_no = #ex.invoice_no
FROM    #ex
WHERE   #book_delivery_order.order_sn = #ex.order_sn


SELECT * FROM #book_delivery_order

[中国风]

用OUTPUT,更新后用INSERTED，更新前用DELETED

update book_delivery_order set invoice_no=ex.invoice_no OUTPUT Inserted.invoice_no,Inserted.order_sn from ex where book_delivery_order.order_sn=ex.order_sn