update 问题
在表A中有两列
A1, A2
01 a01
02 a01
01 b01
02 b02
...
表B中也有两列
B1, B2
a01 b01
b01 c01
b02 c02
....
现在想把表B中与A表的A2与B1相同的数据用B2替换
就是想把A表换成
A1, A2
01 b01
02 b01
01 c01
02 c02
[解决办法]
drop table A,B
go
create table A(A1 varchar(10),A2 varchar(10))
insert into A
select '01 ', 'a01 '
union all select '02 ', 'a01 '
union all select '01 ', 'b01 '
union all select '02 ', 'b02 '
create table B(B1 varchar(10),B2 varchar(10))
insert into B
select 'a01 ', 'b01 '
union all select 'b01 ', 'c01 '
union all select 'b02 ', 'c02 '
update A
set A2=B.B2
from A
inner join B on A.A2=B.B1
select * from A
/*
A1 A2
---------- ----------
01 b01
02 b01
01 c01
02 c02
（所影响的行数为 4 行）
*/
[解决办法]
declare @bb table (A1 varchar(20),A2 varchar(20))
insert into @bb
select '01 ', 'a01 '
union all select '02 ', 'a01 '
union all select '01 ', 'b01 '
union all select '02 ', 'b02 '
declare @cc table(B1 varchar(20),B2 varchar(20))
insert into @cc
select 'a01 ', 'b01 '
union all select 'b01 ', 'c01 '
union all select 'b02 ', 'c02 '
select bb.A1 as A1,cc.B2 as A2
from @bb bb
left join @cc cc on bb.A2=cc.B1
