更新查询-Oracle [英] Update Query - Oracle
问题描述
我无法理解以下SQL查询的问题.我正在尝试将具有表2之间的公共列的表ABC列从表TABLE3复制到表1.
I am not able to understand the issue with the following SQL query. I am trying to copy column ABC from table TABLE3 to TABLE1 with TABLE2 having the common column between the two.
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:无效的标识符"
*原因:
*动作:
SQL Error: ORA-00904: "TC"."ABC": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
因此，我已经更新了查询，以使我的解释更有意义. Table1和Table2通过4列PRD_ID, PRD_VER, PY_ID and TPY_ID连接.由于此组合不是唯一/主键组合，因此可以在Table2中找到多个行.对于从Table2检索到的每一行，common_column列都是更新Table3所需要的，因为common_column仅与一行相关联.
[edit; Please read the explanation below]
So I have updated the query to make more sense w.r.t my explanation. Table1 and Table2 are connected by 4 columns PRD_ID, PRD_VER, PY_ID and TPY_ID. This combination finds multiple rows in Table2 since it is not unique/primary key combination. For each row retrieved from Table2, the column common_column is what is needed to update Table3 since common_column only associates with one row.
示例.
表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应该如下所示:
So after I execute the query, Table3 should look like this:
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.
Update: Other than the changes to the column and table names, my initial answer appears to work with the sample data you posted. Note that it's always easier to post DDL and DML so that we can reproduce your tables and data rather than having us convert your data to DDL and DML.
如果我创建了您的表和数据
If I create your tables and data
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.
然后根据我的最初答案调整表名和列名，看来更新工作正常
Then adjust the table and column names from my initial answer, it appears that the update works correctly
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
这篇关于更新查询-Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
