我猜你要
UPDATe table1 cs SET cs.abc = (SELECT tc.abc FROM table2 cgl, table3 tc WHERe cgl.prd_id = tc.prd_id AND cgl.prd_ver = tc.prd_ver AND cgl.py_id = tc.py_id AND cgl.typ_id = tc.tpy_id AND cd.common_column = cgl.common_column) WHERe EXISTS (SELECt 1 FROM table2 cgl,table3 tc WHERe cgl.prd_id = tc.prd_id AND cgl.prd_ver = tc.prd_ver AND cgl.py_id = tc.py_id AND cgl.typ_id = tc.tpy_id AND cd.common_column = cgl.common_column)
更新：除了对列名和表名的更改之外，我的最初答案似乎适用于您发布的示例数据。请注意，发布DDL和DML总是很容易，以便我们可以重现您的表和数据，而不是让我们将您的数据转换为DDL和DML。
如果我创建您的表和数据
SQL> create table table1 ( 2 prd_id number, 3 prd_ver number, 4 py_id number, 5 typ_id number, 6 column_used_for_update varchar2(10) 7 );Table created.SQL> begin 2 insert into table1 values( 1, 1, 1, 1, 'VALUE1' ); 3 insert into table1 values( 2, 3, 4, 5, 'VALUE2' ); 4 end; 5 /PL/SQL procedure successfully completed.SQL> create table table2 ( 2 prd_id number, 3 prd_ver number, 4 py_id number, 5 typ_id number, 6 common_column varchar2(10) 7 );Table created.SQL> begin 2 insert into table2 values( 1, 1, 1, 1, 'A' ); 3 insert into table2 values( 1, 1, 1, 1, 'B' ); 4 insert into table2 values( 2, 3, 4, 5, 'C' ); 5 end; 6 /PL/SQL procedure successfully completed.SQL> create table table3 ( 2 common_column varchar2(10), 3 column_to_update varchar2(10) 4 );Table created.SQL> begin 2 insert into table3 values( 'A', null ); 3 insert into table3 values( 'B', null ); 4 insert into table3 values( 'C', null ); 5 end; 6 /PL/SQL procedure successfully completed.SQL> commit;Commit complete.
然后根据我的最初答案调整表和列的名称，看来更新正常
SQL> edWrote file afiedt.buf 1 UPDATE table3 t3 2 SET t3.column_to_update = ( 3 SELECT t1.column_used_for_update 4 FROM table2 t2, 5 table1 t1 6 WHERe t1.prd_id = t2.prd_id 7AND t1.prd_ver = t2.prd_ver 8AND t1.py_id = t2.py_id 9AND t1.typ_id = t2.typ_id 10AND t3.common_column = t2.common_column) 11 WHERe EXISTS ( SELECt 1 12 FROM table2 t2, 13 table1 t1 14 WHERe t1.prd_id = t2.prd_id 15AND t1.prd_ver = t2.prd_ver 16AND t1.py_id = t2.py_id 17AND t1.typ_id = t2.typ_id 18* AND t3.common_column = t2.common_column)SQL> /3 rows updated.SQL> select * from table3;COMMON_COL COLUMN_TO_---------- ----------A VALUE1B VALUE1C VALUE2
