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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
392 views