create table a
(
a1 int,
a2 char(20)
);
insert into a values(1,'111111111');
insert into a values(2,'222222222');
insert into a values(3,'333333333');
insert into a values(5,'555555555');
create table b
(
b1 int,
b2 char(20)
);
insert into b values(1,'aaaaaaaaa');
insert into b values(2,'bbbbbbbbb');
insert into b values(3,'ccccccccc');
insert into b values(6,'eeeeeeeee');
select * from a;
A1 A2
----- ------------
1 111111111
2 222222222
3 333333333
5 555555555
select * from b;
B1 B2
------ ----------
1 aaaaaaaaa
2 bbbbbbbbb
3 ccccccccc
6 eeeeeeeee
Update b set b2=(select a2 from a where a.a1=b.b1)
where b1 in(select a1 from a,b where a.a1=b.b1)
select * from b;
B1 B2
----- -----------
1 111111111
2 222222222
3 333333333
6 eeeeeeeee
后面的where in条件一定要有,否则在b表中有而在a表没有的记录
将被置为空
(
a1 int,
a2 char(20)
);
insert into a values(1,'111111111');
insert into a values(2,'222222222');
insert into a values(3,'333333333');
insert into a values(5,'555555555');
create table b
(
b1 int,
b2 char(20)
);
insert into b values(1,'aaaaaaaaa');
insert into b values(2,'bbbbbbbbb');
insert into b values(3,'ccccccccc');
insert into b values(6,'eeeeeeeee');
select * from a;
A1 A2
----- ------------
1 111111111
2 222222222
3 333333333
5 555555555
select * from b;
B1 B2
------ ----------
1 aaaaaaaaa
2 bbbbbbbbb
3 ccccccccc
6 eeeeeeeee
Update b set b2=(select a2 from a where a.a1=b.b1)
where b1 in(select a1 from a,b where a.a1=b.b1)
select * from b;
B1 B2
----- -----------
1 111111111
2 222222222
3 333333333
6 eeeeeeeee
后面的where in条件一定要有,否则在b表中有而在a表没有的记录
将被置为空