同意jiezhi(風依舊) 的写法: update A a set a.id=(select b.id from B b,A a where a.DNO=b.DNO ) where exist (select 1 from B b,A a where a.DNO=b.DNO )
但是没有通过啊,有一个错误,请指正: ORA-01427 single-row subquery returns more than one now.
不是一对一关系,DNO对DNO是一对一, 但ID与ID不是一对一,
试试: update A a set a.id=(select max(b.id) from B b,A a where a.DNO=b.DNO ) where exist (select 1 from B b,A a where a.DNO=b.DNO )ps:oracle的update语句的确不如ms的好用。
我试过 update A a set a.id=(select distinct b.id from B b,A a where a.DNO=b.DNO ) where exist (select 1 from B b,A a where a.DNO=b.DNO ) 也不行啊。
你的表中DNO绝对不是一对一的关系 你可以执行一下 select a.DNO,count(*) from A a,B b where a.DNO=b.DNO group by a.DNO having count(*) > 1 应该会有记录出来 如果没有记录出来才说明你的DNO是一对一的关系
update A set a.id=(select b.id from B where a.DNO=b.DNO )
试试: update A set id=(select id from B where A.DNO=B.DNO);
update A set a.id=(select b.id from B where a.DNO=b.DNO ) where a.DNO=b.DNO
update A a
set a.id=(select b.id from B b,A a where a.DNO=b.DNO )
where exist (select 1 from B b,A a where a.DNO=b.DNO )
ORA-01427 single-row subquery returns more than one now.
但ID与ID不是一对一,
update A a
set a.id=(select max(b.id) from B b,A a where a.DNO=b.DNO )
where exist (select 1 from B b,A a where a.DNO=b.DNO )ps:oracle的update语句的确不如ms的好用。
update A a
set a.id=(select distinct b.id from B b,A a where a.DNO=b.DNO )
where exist (select 1 from B b,A a where a.DNO=b.DNO )
也不行啊。
你可以执行一下
select a.DNO,count(*) from A a,B b where a.DNO=b.DNO group by a.DNO having count(*) > 1
应该会有记录出来
如果没有记录出来才说明你的DNO是一对一的关系
update A set id=(select id from B where A.DNO=B.DNO);
where a.DNO=b.DNO