exists (select 1 from B where A.AID=B.BID)若存在A.AID=B.BID的记录,exists子句返回true,否则返回false;因为只去验证存在不存在记录,因此无所谓select子句中的列,此处仅1代列exists 以短路方式(short_circuit)执行,即A,B表中如果存在多条ID相同的记录,oracle在检索到第一条记录时便返回true.
还可以这么写 merge into A using(select B1, B2 from B) b on (a1=b.B1 and a2=b.B2) when matched then update set AID = b.BID
不好意思,看错了。 应该这么写 update A set A1=B.B1,A2=B.B2 from A,B where A.AID=B.BID merge into A using(select B1, B2,BID from B) b on (AID = b.BID) when matched then update set a1=b.B1 and a2=b.B2
a2=(select b2 from b where a.aid=b.bid)
where exists (select 1 from b where a.aid=b.bid)
where exists (select 1 from B where A.AID=B.BID);
如果没有where条件会全部更新,A.AID<>B.BID的记录会更新为null
merge into A
using(select B1, B2 from B) b
on (a1=b.B1 and a2=b.B2)
when matched then update set AID = b.BID
应该这么写
update A
set A1=B.B1,A2=B.B2 from A,B
where A.AID=B.BID merge into A
using(select B1, B2,BID from B) b
on (AID = b.BID)
when matched then update set a1=b.B1 and a2=b.B2
必须确保=后面的值是unique