求一sql语句

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!

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus