I use Oracle subquery update table times wrong: cannot change with the key values saved table corresponding column, an error screenshot is as follows:


two tables below:


build table statements are as follows:

 create table test1 (id1 int, id2 int, col1 int, col2 int); 
The create table test2 (id1 int, id2 int, v1 int, v2 int); 

initialized data:

 insert into test1 (id1, id2, col1, col2) values (11,12,1001,1002); 
Insert into test1 (id1, id2, col1, col2) values (13,14,1003,1004); 
Insert into test1 (id1, id2, col1, col2) values (15,16,1005,1006); 

Insert into test2 (id1, id2, v1, v2) values (21,22,2001,2002); 
Insert into test2 (id1, id2, v1, v2) values (23,24,2003,2004); 
Insert into test2 (id1, id2, v1, v2) values (25,26,2005,2006); 
Insert into test2 (id1, id2, v1, v2) values (27,28,2007,2008); 

[for] : use the the subquery update table test1, that is, the table of test1 col1 field is modified to test2 in v1 value; Update the condition that test1) id1=test2) id1 and test1. Id2=test2. Id2,

My SQL statement is as follows (execution error, but I don't know why) :

 
The update (
Select a t1 col1, tt. V1 from test1 t1, 
(select id1, id2, Max (v1) v1 from test2 t2 
Group by id1, id2 
Tt) where t1) id1=tt) id1 and t1. Id2=tt. Id2 
T set) tc ol1 t.v=1; 

An error screenshot is as follows:


Excuse me how to use this condition in the Oracle "subquery update table" this kind of grammar?
Do hope who can help to solve it, thanks!

CodePudding user response:

1, test2 table and uniqueness index
2, before oracle11 version can update (
The select/* + BYPASS_UJVC */t1. Col1...

CodePudding user response:

Oracle 12 c version won't be an error, can normal execution,
Oracle version 11.2, an error in the same way as you,
Should be more than 12 c to support this kind of writing you,

CodePudding user response:

With the merge and matching with the rowid update