如何用一个表的某一列更新另一个表的某列(字段名相同)
例如:
table1
waybillcode pn qty
-------------------------------
a001 a1 10
a001 a2 15
a001 a3 5
a002 a4 1
a002 a2 5
......
-------------------------------
table2
waybillcode pn qty
-------------------------------
a001 a1 8
a001 a2 15
a001 a3 5
a002 a2 5
......
-------------------------------
如何用table2更新table1中对应的qty?得到结果如下:
tmpTable
waybillcode pn qty
-------------------------------
a001 a1 8
a001 a2 15
a001 a3 5
a002 a4 0
a002 a2 5
......
-------------------------------
------解决方案-------------------- update table1
set qty=b.qty
from table2 b
where waybillcode=b.waybillcode and pn=b.pn
------解决方案--------------------修改——
update #A
set qty=b.qty
from #A a,#B b
where a.waybillcode=b.waybillcode and a.pn=b.pn
------解决方案--------------------create table #A
(waybillcode varchar(50),
pn varchar(50),
qty int
)
insert into #A
select 'a001 ', 'a1 ', '10 ' union all select 'a001 ', 'a2 ', '15 ' union all select 'a001 ', 'a3 ', '5 ' union all select 'a002 '