两表间的数据更新

两表间的数据更新 [已结贴,结贴人:wai631]

wai631
勇不言败
等级:

发表于：2008-03-11 19:51:16 楼主

有两个表:A、B

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 = (select b.ch from b where a.Aid = b.Bid);
以A表的Aid与B表的Bid为条件(Aid=Bid,除去空的),更新A表的CH列,怎么写SQL?
请高手帮帮忙!

我是这样写的,但是错了
update a set a.ch = (select b.ch from b where a.Aid = b.Bid)

消息 512，级别 16，状态 1，第 1 行
子查询返回的值不止一个。当子查询跟随在 =、!=、 <、 <=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
语句已终止。

问题点数：50 回复次数：7 修改删除举报引用回复

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<>''

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'); go update 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'); go update 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 行） */