我无法理解以下SQL查询的问题。我正在尝试将具有表2之间的公共列的表ABC列从表TABLE3复制到表1。
UPDATE TABLE1 CS SET CS.ABC = TC.ABC WHERE CS.COMMON_COLUMN = ( SELECT CGL.COMMON_COLUMN 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.TPY_ID = TC.TPY_ID )
我遇到了错误：
SQL错误：ORA-00904：“ TC”。“ ABC”：无效的标识符00904。00000-“％s：无效的标识符” 原因： 操作：
[编辑; 请阅读下面的说明]， 因此我更新了查询，使我的说明更合理。Table1和Table2由4列连接PRD_ID, PRD_VER, PY_ID and TPY_ID。由于此组合不是唯一/主键组合，因此可以在Table2中找到多个行。对于从Table2检索到的每一行，该列common_column是更新Table3所需要的，因为它common_column仅与一行相关联。
Table1
Table2
PRD_ID, PRD_VER, PY_ID and TPY_ID
common_column
例子。
表格1
PRD_ID, PRD_VER, PY_ID, TPY_ID, COLUMN_USED_FOR_UPDATE ------------------------------------------------------ 1 , 1 , 1 ,1 , VALUE1 2 , 3 , 4 , 5 , VALUE2
表2
PRD_ID, PRD_VER, PY_ID, TPY_ID, COMMON_COLUMN ------------------------------------------------ 1 , 1 , 1, 1, A 1 , 1 , 1, 1, B 2, 3 , 4, 5, C
表3
COMMON_COLUMN, .... , COLUMN_TO_UPDATE ------------------------------------------------------- A, ..... , null B, .... , null C, .... , null
因此，在执行查询后，Table3应该如下所示：
COMMON_COLUMN, .... , COLUMN_TO_UPDATE ------------------------------------------------------- A, ..... , VALUE1 B, .... , VALUE1 C, .... , VALUE2
我猜你要
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> ed Wrote 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 7 AND t1.prd_ver = t2.prd_ver 8 AND t1.py_id = t2.py_id 9 AND t1.typ_id = t2.typ_id 10 AND 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 15 AND t1.prd_ver = t2.prd_ver 16 AND t1.py_id = t2.py_id 17 AND 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 VALUE1 B VALUE1 C VALUE2
