create table a( no number notnull, name varchar(10) notnull, loc varchar(10) notnull);
create table b( no number notnull, name varchar(10) notnull, loc varchar(10) notnull);
insert into a values(10, 'AAA', 'Aremak1');
insert into a values(20, 'BBB', 'Aremak2');
insert into a values(30, 'CCC', 'Aremak3');
insert into b values(1, 'AAA', 'Bremak1');
insert into b values(2, 'BBB', 'Bremak2');
insert into b values(3, 'DDD', 'Bremak3');
SELECT * FROM a;
ID NAME LOC
---------- ---------- ----------
10 AAA Aremak1
20 BBB Aremak2
30 CCC Aremak3
SELECT * FROM b;
ID NAME LOC
---------- ---------- ----------
1 AAA Bremak1
2 BBB Bremak2
3 DDD Bremak3
更新b表,使name相同的id更新为a表的id
oracle下sql1
update b set id = (select a.id from a where a.name=b.name);
上述语句更新的时候,因为name为‘DDD’记录在a表中没有,这行无法获取数据,会把id设为null
ID NAME LOC
---------- ---------- ----------
10 AAA Bremak1
20 BBB Bremak2
DDD Bremak3
为了避免这个错误需要更改sql1为sql2
update b set id = (select a.id from a where a.name=b.name) where exists (select 1 from a where a.name=b.name);
ID NAME LOC
---------- ---------- ----------
10 AAA Bremak1
20 BBB Bremak2
3 DDD Bremak3
错误总结:
1.sql1 (select a.id from 后面不能跟a,b) update b set id = (select a.id from a,b where a.name=b.name);
会导致错误 single-row subquery returns more than one row
2.sql2 exists 后面的语句中也不能跟a,b select 1 from a,b where a.name=b.name,
会导致该exists语句无效,但是不报错。