表结构： CREATE TABLE ATEST (ID INTEGER, NAME VARCHAR(256), CODE INTEGER, NAME2 VARCHAR(256) )
CREATE TABLE BTEST (ID INTEGER, CODE INTEGER )
CREATE TABLE CTEST (ID INTEGER, NAME VARCHAR(256), NAME2 VARCHAR(256) )
SQL语句： 一张表更新另一张表的字段： update atest set atest.name=(select ctest.name from ctest where atest.id = ctest.id) where atest.id in (select ctest.id from ctest);
两张表关联更新另一张表的字段： update atest set (name,name2) = (SELECT CASE WHEN CTEST.NAME IS NULL THEN ATEST.NAME ELSE CTEST.NAME END, CASE WHEN CTEST.NAME2 IS NULL THEN ATEST.NAME2 ELSE CTEST.NAME2 END FROM BTEST LEFT JOIN CTEST on BTEST.ID = CTEST.ID WHERE atest.CODE = BTEST.CODE) WHERE atest.CODE IN (SELECT BTEST.CODE FROM BTEST);
另外一个: http://blog.csdn.net/Bobwu/archive/2009/01/13/3768636.aspx
1. declare cursor t1 is select * from tablename; begin for rec in t1 loop update tablename t set t.detail=rec.jieshao where t.objectid=rec.objid; end loop; end;
2.
update student set (name,id )= (select name ,id from (select student.rowid rd,student1.name,student1.id from student1,student where student1.int_id =student.int_id) tmp where student.rowid=tmp.rd); commit;
3.
update test_a a set (a.name,a.age)= (select b.name,b.age from test_b b where a.id = b.id) where exists (select * from test_b c where c.id=a.id)
4.
UPDATE t_A SET Djrq= ( SELECT djrq FROM t_B WHERE t_A.ID = T_B.ID WHERE ROWNUM = 1 ) WHERE t_A.ID IN ( SELECT ID FROM t_B WHERE jwh='XX村' )
5.
update tbl1 a set (a.col1, a.col2) = (select b.col1, b.col2 from tbl2 b where a.key = b.key) where a.key in(select key from tbl2)