0
create table target(id number, name varchar2(10));

insert into target values (1, 'Vinay');

create table source(id number, name varchar2(10)); 

insert into source values (1, 'sql');

insert into source values (1, 'oracle'); 

merge into target t 
using (select * from source) s   
  on (t.id = s.id) 
when matched then update set t.name = s.name;

I am getting ora-30296 error but I need to update rows of the target table even if that id is duplicated in the source table. Is there any other way where I can do this instead of merging? If the source table contains id as 1 with two different values then in the target table latest records should be present. I am wondering how to achieve this?

Vicky
  • 639
  • 3
  • 13
  • 1
    Does this answer your question? [ORA-30926: unable to get a stable set of rows in the source tables](https://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables) – Nora Na Nov 10 '21 at 05:10
  • @NoraNa When I am giving select distinct id from source in using clause then it is giving me s.name invalid identifier error – Vicky Nov 10 '21 at 05:51
  • 1
    Need to catch the uniqueness for the matching criteria as fetching each row. [This](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=5918cde6b73344f689a98e2f2e823ec1) might be a workaround for your case – Barbaros Özhan Nov 10 '21 at 15:50

0 Answers0