Table structure: CREATE table atest (ID integer, NAME VARCHAR, CODE INTEGER, NAME2 varchar (256))
CREATE TABLE btest (ID integer, CODE integer)
CREATE TABLE ctest (ID INTEGER, NAME VARCHAR, NAME2 varchar (256))
SQL statement: One table updates the fields of another table: Update atest set atest.name= (select Ctest.name from ctest where atest.id = ctest.id) where atest.id I N (select ctest.id from CTest);
Two table associations update the fields of the other table: Update atest set (name,name2) = (SELECT case when Ctest.name was NULL then Atest.name ELSE ctest.name END, Case when CTest. NAME2 is NULL and 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);
The other one: 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 wher E ROWNUM = 1) where t_a.id in (SELECT ID from t_b WHERE jwh= ' xx village ')
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)
DB2 updating data for other tables with a single table