Skip to Main Content
  • Questions
  • Update rows using MERGE on rows that do not have a unique identifier

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Tonya.

Asked: October 19, 2016 - 4:04 pm UTC

Last updated: May 21, 2018 - 11:50 am UTC

Version: 11i

Viewed 10K+ times! This question is

You Asked

I have an external table that reads from a csv file. I then need to merge any updates or new rows to a table. The problem is the table does not have a unique identifier. I have account numbers and dates, but the date may get updated on an account number that may have multiple entries. For example:

MERGE INTO table p
USING(
SELECT DISTINCT account_name
, account_no
, amount
, bank_date
, date_applied
, update_date
FROM ext_table
WHERE update_date = to_date(sysdate, 'dd-Mon-yyyy') e
ON (p.account_no = e.account_no)
WHEN MATCHED THEN
UPDATE
SET p.account_name = e.account_name
, p.amount = e.amount
, p.bank_date = e.bank_date
, p.date_applied = e.date_applied
, p.update_date = e.update_date
;

The problem is that I am not able to just match the rows on the account numbers in the table with the rows in the external table that have an update date, because I have duplicate account numbers. Therefore, when I execute the merge statement all rows that match the account number will get updated, not just the rows that have been updated in the csv file(external table). All columns, but the account number has the potential to be updated. Is there another way to do this?

and Connor said...

You need a 'stable' set of rows, ie one row for each join because otherwise the results of the merge would be dependent on which rows we processed first, which makes the whole thing "random".


SQL> drop table t purge;

Table dropped.

SQL> drop table src purge;

Table dropped.

SQL>
SQL> create table src ( acct int, upd date, z int );

Table created.

SQL>
SQL> insert into src values (1, date '2016-01-01', 1 );

1 row created.

SQL> insert into src values (1, date '2016-01-02', 2 );

1 row created.

SQL> insert into src values (10, date '2016-01-01', 11 );

1 row created.

SQL> insert into src values (11, date '2016-01-02', 12 );

1 row created.

SQL> insert into src values (12, date '2016-01-02', 12 );

1 row created.

SQL>
SQL> create table t ( tacct int primary key, tupd date, tz int );

Table created.

SQL>
SQL> insert into t values (1, date '2015-01-01', 1 );

1 row created.

SQL> insert into t values (10, date '2015-01-01', 11 );

1 row created.

SQL> insert into t values (11, date '2015-01-02', 12 );

1 row created.

SQL>
SQL>
SQL> merge into t
  2  using
  3    ( select distinct acct, upd, z from src ) s
  4  on ( t.tacct = s.acct )
  5  when matched
  6    then update set tz = z, tupd = upd
  7  /
merge into t
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables


So we need an inner query that gets the data we need for the merge consistently. Here's an *example* where we pick up the latest data per account , where "latest" is defined by updated date.

SQL>
SQL> select  acct, max(upd), max(z) keep ( dense_rank last order by upd )  from src
  2  group by acct
  3  /

      ACCT MAX(UPD)  MAX(Z)KEEP(DENSE_RANKLASTORDERBYUPD)
---------- --------- ------------------------------------
         1 02-JAN-16                                    2
        10 01-JAN-16                                   11
        11 02-JAN-16                                   12
        12 02-JAN-16                                   12

4 rows selected.


We can then use this to do our merge

SQL>
SQL> merge into t
  2  using
  3    ( select  acct, max(upd) upd, max(z) keep ( dense_rank last order by upd ) z from src
  4      group by acct
  5    ) s
  6  on ( t.tacct = s.acct )
  7  when matched
  8    then update set tz = z, tupd = upd
  9  /

3 rows merged.

SQL>
SQL> select * from t;

     TACCT TUPD              TZ
---------- --------- ----------
         1 02-JAN-16          2
        10 01-JAN-16         11
        11 02-JAN-16         12

3 rows selected.

SQL>
SQL>



Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Does T have a primary key?

Stew Ashton, October 20, 2016 - 6:43 am UTC

After reading the question a few times, I get the impression that the target table has multiple rows per account number. The answer deals with multiple rows in the source table.

Could the OP make it clear who has the duplicates? and if it is the target, how to decide which row to update?

Best regards, Stew
Chris Saxon
October 20, 2016 - 1:10 pm UTC

Good questions Stew; it's not clear to me exactly where the duplicates are!

Tonya Smith, October 20, 2016 - 2:07 pm UTC

Thank you very much for your response and resolution. I am working with the business to create another column to get stable set or rows.
Chris Saxon
October 20, 2016 - 4:07 pm UTC

Great, let us know if you're still struggling.

any reason why no ORA-30926 ?

Rajeshwaran, Jeyabal, April 19, 2018 - 1:01 pm UTC

Team,

Any specific reason why don't we hit ORA-30926 for this case ?

demo@ORA11G> create table t1( x int primary key, y int);

Table created.

demo@ORA11G> create table t2( x int, y int);

Table created.

demo@ORA11G> insert into t1(x,y) values(1,1);

1 row created.

demo@ORA11G> insert into t2(x,y) values(1,1);

1 row created.

demo@ORA11G> insert into t2(x,y) values(1,2);

1 row created.

demo@ORA11G> select * from t1;

         X          Y
---------- ----------
         1          1

demo@ORA11G> select * from t2;

         X          Y
---------- ----------
         1          1
         1          2

demo@ORA11G> merge into t1 using t2
  2  on (t1.x = t2.x)
  3  when matched then
  4     update set t1.y = t2.y;

2 rows merged.

demo@ORA11G> select * from t1;

         X          Y
---------- ----------
         1          2

demo@ORA11G> select * from t2;

         X          Y
---------- ----------
         1          1
         1          2

demo@ORA11G>

Connor McDonald
May 14, 2018 - 1:26 am UTC

I'm not sure where I sit on this one. It sort of depends on one's definition of deterministic.

With T2, the merge can proceed as:

SQL> select * from t2;

        X          Y
---------- ----------
         1          1  => "No operation required, data unchanged"
         1          2  => "Row to be changed"



and hence there is only a single update occurring, so we deem it to be deterministic.

If I change the operation so that *two* updates would occur,

SQL> select * from t2;

        X          Y
---------- ----------
         1          2  => "Row to be changed"
         1          3  => "Row to be changed"



then I'll get the ORA-30926 as expected.

I'll ask around internally to get peoples thoughts on the first case - I'm inclined to think it should also send back ORA-30926

Ouch

Racer I., May 14, 2018 - 10:25 am UTC

Hi,

merge into t1 using (select x, y from t2 order by y asc) t2
on (t1.x = t2.x)
when matched then
update set t1.y = t2.y;

ORA-30926

merge into t1 using (select x, y from t2 order by y desc) t2
on (t1.x = t2.x)
when matched then
update set t1.y = t2.y;

2 rows updated,

Looks like the ORA-30926 is not based on meta-data (unique constraints, etc.) but on runtime data. And not on things like GROUP BY HAVING COUNT() > 1 but on hitting the same row again. But only if it was updated. Similar to how you can only DELETE merged rows, that have actually been updated.

That may explain the order dependence
- 1 (no update) then 2 (update) is ok.
- 2 (update) then 1 (update again) errors out.

Also : without the ORDER BY I got the error always.

Shudder.

regards,
Connor McDonald
May 18, 2018 - 2:12 am UTC

Agreed

with NULL's / Differeing values on Column Y/ Identical values on Column Y

Rajeshwaran, Jeyabal, May 20, 2018 - 6:16 am UTC

Team,

few more details/scenarios.

with NULL in place, it works perfect.

demo@ORA12C> create table t1(x int,y int);

Table created.

demo@ORA12C> create table t2(x int,y int);

Table created.

demo@ORA12C> insert into t1(x,y) values(1,null);

1 row created.

demo@ORA12C> insert into t2(x,y) values(1,1);

1 row created.

demo@ORA12C> insert into t2(x,y) values(1,2);

1 row created.

demo@ORA12C> select * from t1;

         X          Y
---------- ----------
         1

demo@ORA12C> select * from t2;

         X          Y
---------- ----------
         1          1
         1          2

demo@ORA12C> merge into t1 using t2
  2  on (t1.x = t2.x)
  3  when matched then
  4  update set t1.y = t2.y ;
merge into t1 using t2
                    *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables


if source and target table has the same values on the column to be merged - then it breaks like this.

demo@ORA12C> update t1 set y = 1 where y is null;

1 row updated.

demo@ORA12C> select * from t1;

         X          Y
---------- ----------
         1          1

demo@ORA12C> select * from t2;

         X          Y
---------- ----------
         1          1
         1          2

demo@ORA12C> merge into t1 using t2
  2  on (t1.x = t2.x)
  3  when matched then
  4  update set t1.y = t2.y ;

2 rows merged.

demo@ORA12C> select * from t1;

         X          Y
---------- ----------
         1          2

demo@ORA12C>


if source and target has the different values on the column to be merged - then it works perfect.

demo@ORA12C> create table t1(x int,y int);

Table created.

demo@ORA12C> create table t2(x int,y int);

Table created.

demo@ORA12C>
demo@ORA12C> insert into t1(x,y) values(1,null);

1 row created.

demo@ORA12C> insert into t2(x,y) values(1,1);

1 row created.

demo@ORA12C> insert into t2(x,y) values(1,2);

1 row created.

demo@ORA12C> select * from t1;

         X          Y
---------- ----------
         1

demo@ORA12C> select * from t2;

         X          Y
---------- ----------
         1          1
         1          2

demo@ORA12C> merge into t1 using t2
  2  on (t1.x = t2.x)
  3  when matched then
  4  update set t1.y = t2.y ;
merge into t1 using t2
                    *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables


demo@ORA12C> update t1 set y = 5 where y is null;

1 row updated.

demo@ORA12C> select * from t1;

         X          Y
---------- ----------
         1          5

demo@ORA12C> select * from t2;

         X          Y
---------- ----------
         1          1
         1          2

demo@ORA12C> merge into t1 using t2
  2  on (t1.x = t2.x)
  3  when matched then
  4  update set t1.y = t2.y ;
merge into t1 using t2
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables


demo@ORA12C>

Connor McDonald
May 21, 2018 - 11:50 am UTC

I'd encourage you to log a bug (customers logging bugs carries a lot more weight than me doing so).

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.