2

I have a query to Update a table with a Select from another table, This is my query:

MERGE INTO tb1 USING tb2 ON (
        tb1.id = tb2.id
    AND
        tb2.name IS NOT NULL
    AND
        tb2.val = (
            SELECT
                MAX(val)
            FROM
                tb2
        )
) WHEN MATCHED THEN UPDATE
SET tb1.name = tb2.name,
    tb1.birthday = tb2.birthday;

But, sometimes I have this error from the logs:

ORA-30926: unable to get a stable set of rows in the source tables

jkdev
  • 11,360
  • 15
  • 54
  • 77
Luu Dung Ha
  • 41
  • 5

2 Answers2

1

Check for the duplicates in your join query:

select tb1.id, count(*)
from   tb1 join tb2
         on  tb1.id = tb2.id
         AND tb2.name IS NOT NULL
         AND tb2.val = (SELECT MAX(val) FROM tb2)
group by tb1.id
having count(*) > 1

If any data is returned then this is the case.

Maxim Borunov
  • 901
  • 5
  • 9
1

I found the way to resolve my problem:

MERGE INTO tb1 USING 
(SELECT * FROM tb2 WHERE 
   tb2.name IS NOT NULL
    AND
        tb2.val = (
            SELECT
                MAX(val)
            FROM
                tb2
)) tb2 
ON ( tb1.id = tb2.id) 
WHEN MATCHED THEN UPDATE
SET tb1.name = tb2.name,
    tb1.birthday = tb2.birthday;

I use subquery from the tb2 before merge, then update.

Luu Dung Ha
  • 41
  • 5