create table a( Aid char(2), ch char(1))
create table b( Bid char(2), ch char(1))insert into a values(1, 'a');
insert into a values(2, 'b');
insert into a values(6, 'c');
insert into a values('', 'd');
insert into b values(1, '3');
insert into b values(2, '4');
insert into b values(3, '5');
insert into b values(4, '6');
insert into b values('', '7');
insert into b values('', '8'); update a set a.ch = b.ch from a join b on a.Aid = b.Bid where a.aid<>''1 3
2 4
6 c
d
create table b( Bid char(2), ch char(1))insert into a values(1, 'a');
insert into a values(2, 'b');
insert into a values(6, 'c');
insert into a values('', 'd');
insert into b values(1, '3');
insert into b values(2, '4');
insert into b values(3, '5');
insert into b values(4, '6');
insert into b values('', '7');
insert into b values('', '8'); update a set a.ch = b.ch from a join b on a.Aid = b.Bid where a.aid<>''1 3
2 4
6 c
d
create table b( Bid char(2), ch char(1))insert into a values(1, 'a');
insert into a values(2, 'b');
insert into a values(6, 'c');
insert into a values('', 'd');
insert into b values(1, '3');
insert into b values(2, '4');
insert into b values(3, '5');
insert into b values(4, '6');
insert into b values('', '7');
insert into b values('', '8'); update a set a.ch = b.ch from a join b on a.Aid = b.Bid where a.aid<>''
--even if multy rows
update a set ch = b.ch from a,b where a.Aid = b.Bid and a.aid<>''
create table b( Bid char(2), ch char(1))
insert into a values(1, 'a');
insert into a values(2, 'b');
insert into a values(6, 'c');
insert into a values('', 'd');
insert into b values(1, '3');
insert into b values(2, '4');
insert into b values(3, '5');
insert into b values(4, '6');
insert into b values('', '7');
insert into b values('', '8');
goupdate a
set a.ch = b.ch
from a, b
where a.aid = b.bid and a.aid <> '' ;select * from a drop table a,b/*
Aid ch
---- ----
1 3
2 4
6 c
d(所影响的行数为 4 行)
*/
create table a( Aid char(2), ch char(1))
create table b( Bid char(2), ch char(1))
insert into a values(1, 'a');
insert into a values(2, 'b');
insert into a values(6, 'c');
insert into a values('', 'd');
insert into b values(1, '3');
insert into b values(2, '4');
insert into b values(3, '5');
insert into b values(4, '6');
insert into b values('', '7');
insert into b values('', '8');
goupdate a set a.ch = isnull((select b.ch from b where a.Aid = b.Bid),a.ch) where a.aid <> ''select * from a drop table a,b/*
Aid ch
---- ----
1 3
2 4
6 c
d(所影响的行数为 4 行)
*/
set ch=b.ch
from a,b where a.aid=b.bid--或者加上top 1
update a set a.ch = (select top 1 b.ch from b where a.Aid = b.Bid)