假设我有个表a(aid,aname,acountry)和表b(bid,bname)分别有记录如下:
a (aid aneme acountry) b (bid,bname )
( 1 lily china ) ( 1 china )
( 2 lucy china ) ( 2 japan )
( 3 leo china ) ( 3 America)
( 4 lan America) ( 4 england)
( 5 les japan ) (...... )
(...... )
要怎写sql语句,可以进行批量更新将acountry字段记录批量改为对应的bid改成后的a表如下:
a (aid aneme acountry)
( 1 lily 1 )
( 2 lucy 1 )
( 3 leo 1 )
( 4 lan 3 )
( 5 les 2 )
(...... )
a (aid aneme acountry) b (bid,bname )
( 1 lily china ) ( 1 china )
( 2 lucy china ) ( 2 japan )
( 3 leo china ) ( 3 America)
( 4 lan America) ( 4 england)
( 5 les japan ) (...... )
(...... )
要怎写sql语句,可以进行批量更新将acountry字段记录批量改为对应的bid改成后的a表如下:
a (aid aneme acountry)
( 1 lily 1 )
( 2 lucy 1 )
( 3 leo 1 )
( 4 lan 3 )
( 5 les 2 )
(...... )
set a.acountry = b.bid
from a join b on a.aneme = b.bname
set a.acountry = b.bid
from a , b
where a.aneme = b.bname
貌似只能用name连接了
update a
set a.acountry = (select top 1 bid from b where bname = a.aneme)
update a set acountry = b.bid from a inner join b on a.acountry = b.bname
--没看出更新的作用是啥- -。查询时直接关联查不是满好的。一定要更新的话
update a set acountry=b.bid
from a,b where a.aname=b.bname