Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UPDATE query with correlated subquery produces wrong update count instead of raising ORA-30926 or ORA-01427

Lukas EderNov 14 2023

This seems to be a regression introduced in 21c or 19c. I cannot reproduce it with 18c. Try this sequence of statements:

create table t (i int primary key, j int); 
create table u (x int references t, y int); 
insert into t (i) values (2); 
insert into u (x, y) values (2, 2); 

update t 
set t.j = (select y from u where u.x = t.i) 
where t.i = 2; 

insert into u (x, y) values (2, 3); 

update t 
set t.j = (select y from u where u.x = t.i) 
where t.i = 2;

The last update statement produces an update count of 2 with Oracle 21c and 23c (using https://hub.docker.com/r/gvenzl/oracle-free or dbfiddle). It produces ORA-01427 in 18c (see https://dbfiddle.uk/pvqC6bNT).

Running the last update statement again produces the following error on 21c and 23c:

ORA-30926: The operation attempted to update the same row (rowid: 'AAASDOAAPAAAANfAAA') twice.

Which I guess is an OK alternative to ORA-04127

This post has been answered by gsalem-Oracle on Nov 14 2023
Jump to Answer
Comments
Post Details
Added on Nov 14 2023
3 comments
594 views