table1
bm1 varchar
je1 number
table2
bm2 varchar
je2 number
我要把bm1 = bm2 的je1 设成 je2 应怎么写这个update 语句
---------------------------------------------------------------
用游标吧!
---------------------------------------------------------------
Update table1 A Set je1=(select je2 from table table2 where bm2=A.bm1 and rownum=1)
或者
Update table1 A Set je1=Nvl((select je2 from table table2 where bm2=A.bm1 and rownum=1),je1)
数据量在十万级以下比较快.
---------------------------------------------------------------
update table1 setje1=(select je2 from table2 where table1.bm1=table2.bm2);
commit ;
---------------------------------------------------------------
update table1
set je1 = (select je2 from table2 where bm1 = bm2)
---------------------------------------------------------------
同意上述
---------------------------------------------------------------
"那我要是不只有一个字段呢,就的不只用一个子语句。":
update a set (FLD1,FLD2,FLD3)=(select FLD1,FLD2,FLD3 from b where a.id=b.id)
where exists ( select 1 from b where a.a_id=b.a_id);
---------------------------------------------------------------
update set table1 a set (a.col1,a.col2,...)=(select b.col1,b.col2,... from table2 b where a.bm1=b.bm2);
commit ;
---------------------------------------------------------------
very good!