SQL & PL/SQL

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!

How to use MERGE with the same table

Filipe RBJan 2 2019 — edited Jan 3 2019

I have an error table that I would like to update the CNT_ERROR field as follow:

MERGE INTO PL_HISTERROR_LINCOLASSOC_01 TblUpd
   USING (
      SELECT NUM_ASSOC_FORM
            ,NUM_ASSOC
            ,COUNT(*) OVER (PARTITION BY NUM_ASSOC_FORM, NUM_ASSOC) CNT_ERROR
      FROM PL_HISTERROR_LINCOLASSOC_01
   ) SubQry
   ON (TblUpd.NUM_ASSOC_FORM = SubQry.NUM_ASSOC_FORM AND TblUpd.NUM_ASSOC = SubQry.NUM_ASSOC)                
   WHEN MATCHED THEN
      UPDATE SET TblUpd.CNT_ERROR = SubQry.CNT_ERROR;

but I am getting the following error:

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

How can I avoid getting this error ?

This post has been answered by mathguy on Jan 2 2019

Jump to Answer

Added on Jan 2 2019

#sql

6 comments

1,521 views