更新查询-Oracle

我猜你要

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