update tmpa a set a.columna = (select columna from tmpb b where a.columnb=b.columnb and b.updateflag=1 ) where a.columnb in (select columnb from tmpb)
update tmpa a set a.columna='OK' where a.columnb in (select t.columnb from tmpa t,tmpb b where b.updateflag=1 and b.columnb = t.columnb)
你这个问题说的不清楚:用什么来更新columna? 应该是一个新值吧,那么很好办。 UPDATE tmpa SET columna = new_value WHERE tmpa.columnb IN (SELECT columnb FROM tmpb WHERE updateflag = 1 )
sorry,忘了写了, 是将tmpb 的columna update过来
update tmpa set columna = new_value where exists (select * from tmpb where tmpa.columnb=tmpb.columnb and tmpb.updateflag=1);
create table tab1(id varchar2(20),key varchar2(20))create table tab2(id2 varchar2(20),key2 varchar2(20))insert into tab1 values('001','0001') insert into tab1 values('002','0002'); insert into tab1 values('003','0003'); insert into tab1 values('004','0004');insert into tab2 values('001','00001'); insert into tab2 values('002','00002'); insert into tab2 values('003','00003');select * from tab1select * from tab2--错误没有的被更新为null update tab1 a set key=(select key2 from tab2 b where a.id=b.id2)--正确只有相等才会被更新 update tab1 a set key=(select key2 from tab2 b where a.id=b.id2) where exists (select 1 from tab2 b where a.id=b.id2)
update tmpa set columna = (select tmpb.columna from tmpb where tmpa.columnb=tmpb.columnb and tmpb.updateflag=1) where exists (select * from tmpb where tmpa.columnb=tmpb.columnb and tmpb.updateflag=1);
update tmpa set columna = (select tmpb.columna from tmpb where tmpa.columnb=tmpb.columnb and tmpb.updateflag=1) where exists (select columnb from tmpb where tmpa.columnb=tmpb.columnb and tmpb.updateflag=1);
update tmpa a set a.columna=(select b.columna from tmpb b where a.columnb=b.columnb and b.updateflag=1) where exists (select 1 from tmpb b where a.columnb=b.columnb and b.updateflag=1);
update tmpa set columna=(select a.columna from tmpa a tmpb b where a.columb=b.columb and b.updateflag=1)
update tmpa a set a.columna=(select b.columna from tmpa a,tmpb b where a.columnb=b.columnb and b.updateflag=1) where a.columnb in (select columnb from tmpb where updateflag=1) 想错了,不好意思,这个可以
set a.columna = (select columna from tmpb b where a.columnb=b.columnb and
b.updateflag=1 )
where a.columnb in (select columnb from tmpb)
应该是一个新值吧,那么很好办。
UPDATE tmpa SET columna = new_value
WHERE tmpa.columnb IN
(SELECT columnb
FROM tmpb
WHERE updateflag = 1
)
是将tmpb 的columna update过来
insert into tab1 values('002','0002');
insert into tab1 values('003','0003');
insert into tab1 values('004','0004');insert into tab2 values('001','00001');
insert into tab2 values('002','00002');
insert into tab2 values('003','00003');select * from tab1select * from tab2--错误没有的被更新为null
update tab1 a
set key=(select key2 from tab2 b where a.id=b.id2)--正确只有相等才会被更新
update tab1 a
set key=(select key2 from tab2 b where a.id=b.id2)
where exists (select 1 from tab2 b where a.id=b.id2)
where exists (select * from tmpb where tmpa.columnb=tmpb.columnb and tmpb.updateflag=1);
where exists (select columnb from tmpb where tmpa.columnb=tmpb.columnb and tmpb.updateflag=1);