SQL

Question

A表数据
ser_no          p_cost          cost       col1   col2
---------       -------         ------    -----   -----
2013090901 100.0000 100.0000   NULL    NULL
2013090902 200.0000 300.0000   NULL    NULL
2013090903 55.0000         120.0000   NULL    NULL
2013090904 150.0000 150.0000   NULL    NULLB表数据
ser_no          price1          price2
---------       -------         -------
2013090901 0.0000         0.0000
2013090902 0.0000         0.0000
2013090903 0.0000         0.0000
2013090904 0.0000         0.0000使用SQL，将B表的price1列和price2列的值更新为A表对应（通过两个表的ser_no关联）的p_cost列和cost的值，更新后B表的值为：
ser_no          price1          price2
---------       -------         -------
2013090901 100.0000 100.0000  
2013090902 200.0000 300.0000   
2013090903 55.0000         120.0000   
2013090904 150.0000 150.0000   怎么使用一个SQL是实现？

Answer 1

update B
set price1=p_cost,price2=cost       
from B
inner join A on A.ser_no=B.ser_no

Answer 2

create table #t1(ser_no varchar(10), p_cost money,cost money,col1 varchar(10),col2 varchar(10))
insert into #t1
select '2013090901',100.0000,100.0000,NULL,NULL
union all select '2013090902',200.0000,300.0000,NULL,NULL
union all select '2013090903',55.0000,120.0000,NULL,NULL
union all select '2013090904',150.0000,150.0000,NULL,NULLcreate table #t2(ser_no varchar(10), price1 money,price2 money)
insert into #t2
select '2013090901',0.0000,0.0000
union all select '2013090902',0.0000,0.0000
union all select '2013090903',0.0000,0.0000
union all select '2013090904',0.0000,0.0000
update B
set price1=p_cost,price2=cost       
from #t2 B
inner join #t1 A on A.ser_no=B.ser_no
drop table #t1,#t2/*
ser_no          price1          price2
----------------------------------------
2013090901 100.0000 100.0000
2013090902 200.0000 300.0000
2013090903 55.0000 120.0000
2013090904 150.0000 150.0000
*/