0

I have a merge query as follows:

    merge into table1
    using table2
    on (table1.column1 = table2.column1)
    when mateched then
    update
   set column2 = table2.column2;

Now it is giving me error like :unable to get a stable set of rows in the source tables

Now the issue is the source table, table2, is having multiple records for same column1 value and table1 is having only one record per column1 value.And I need all the table2 to update table1. can you pls help here?

APC
  • 144,005
  • 19
  • 170
  • 281
Jogesh
  • 9
  • 4
  • Which row from table 2 will you use to update table 1, when there are multiples? Your `using` clause just needs to be a subquery that identifies and returns just one row with the `column2` value you actually want to use. – Alex Poole Jun 22 '17 at 14:04
  • @Alex Poole I need all records from table2 to update table1. The reason is that I have a trigger on table1 which captures all transactions(inserts/updates) and loads to another history table. – Jogesh Jun 22 '17 at 14:08
  • 1
    You can't do that. Once you modify the source row, subsequent matches will get that error. You'll need to find another mechanism. – Alex Poole Jun 22 '17 at 14:15
  • [`MERGE` is a deterministic statement. You cannot update the same row of the target table multiple times in the same `MERGE` statement.](https://docs.oracle.com/database/121/SQLRF/statements_9017.htm#SQLRF01606) – Alex Poole Jun 22 '17 at 14:28

1 Answers1

2

" the issue is the source tabletable2, is having multiple records for same column1 value "

You need to ensure your sub-query returns only one row per value of column1. Only you know what exact business rule you need to apply, but it could be something like this:

merge into table1
using ( select column1, max(column2) as column2
        from table2
        group by column1 ) t2
on (table1.column1 = t2.column1)
when matched then
update
set column2 = t2.column2;

"I need all records from table2 to update table1. The reason is that I have a trigger on table1 which captures all transactions(inserts/updates) and loads to another history table. "

You can't use MERGE. In fact you're probably going to have to go procedural:

begin
    for t2rec in (select column1, column2
                  from table2
                  order by column1, column2 )
    loop
       update table1 
       set column2 = t2rec.column2
       where column1 = t2rec.column1;
    end loop;
end;

You'll need to order the loop statement to ensure the end state of table1 is right.

Alternatively you could disable the journaling trigger and do this:

insert into table1_journal
select table1.column1
       , table2.column2
from table1 
     join table2 
     on  table1.column1 = table2.column1
/

Then do the MERGE or a single row update.

APC
  • 144,005
  • 19
  • 170
  • 281
  • I need all records from table2 to update table1. The reason is that I have a trigger on table1 which captures all transactions(inserts/updates) and loads to another history table. – Jogesh Jun 22 '17 at 14:09
  • 2
    Then you can't use MERGE. – APC Jun 22 '17 at 14:13