表結構：
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)