Skip to Main Content

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!

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

Karthik.CMMay 16 2021

I am executing a merge query to update 2 columns in a table, but I get the following error "ORA-30926: unable to get a stable set of rows in the source tables. When I execute the merge query but I have already used a partition by and where rn=1 in the using clause to pick up only the non-duplicate records from source, but oracle still throws the error can someone please help me on this?

MERGE
/*+ parallel(A) enable_parallel_dml*/
INTO
(
  SELECT
    PAY_RANGE_START_DATE_KEY,
    AA_PERSON_NATURAL_KEY,
    AA_PERSON_ASSIGNMENT_KEY,
    SCHEDULE_LINE_ID,
    SRC_CREATED_DATE,
    SRC_LAST_UPDATE_DATE
  FROM
    EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP
)
A USING
(
SELECT
  PAY_RANGE_START_DATE_KEY,
  AA_PERSON_NATURAL_KEY,
  AA_PERSON_ASSIGNMENT_KEY,
  SCHEDULE_LINE_ID,
  SRC_CREATED_DATE,
  SRC_LAST_UPDATE_DATE
FROM
  (
    SELECT
      PAY_RANGE_START_DATE_KEY,
      AA_PERSON_NATURAL_KEY,
      AA_PERSON_ASSIGNMENT_KEY,
      SCHEDULE_LINE_ID,
      SRC_CREATED_DATE,
      SRC_LAST_UPDATE_DATE,
      ROW_NUMBER() OVER ( PARTITION BY PAY_RANGE_START_DATE_KEY,
      AA_PERSON_NATURAL_KEY, AA_PERSON_ASSIGNMENT_KEY, SCHEDULE_LINE_ID,
      SRC_CREATED_DATE, SRC_LAST_UPDATE_DATE ORDER BY ROWNUM ) AS rn
    FROM
      EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP_FRS_356
  )
WHERE
  rn = 1
)
B ON
(
  A.PAY_RANGE_START_DATE_KEY = B.PAY_RANGE_START_DATE_KEY AND
  A.AA_PERSON_NATURAL_KEY    = B.AA_PERSON_NATURAL_KEY AND
  A.AA_PERSON_ASSIGNMENT_KEY = B.AA_PERSON_ASSIGNMENT_KEY AND
  A.SCHEDULE_LINE_ID         = B.SCHEDULE_LINE_ID
)
WHEN MATCHED THEN
  UPDATE
  SET
    A.SRC_CREATED_DATE     = B.SRC_CREATED_DATE,
    A.SRC_LAST_UPDATE_DATE = B.SRC_LAST_UPDATE_DATE
  WHERE
    A.SRC_CREATED_DATE      <> B.SRC_CREATED_DATE
  OR A.SRC_LAST_UPDATE_DATE <> B.SRC_LAST_UPDATE_DATE;
Comments
Post Details
Added on May 16 2021
4 comments
4,686 views