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

ORA-30926: The operation attempted to update the same row (rowid: 'AAASBxAAYAAAAAMAAA') twice.

One target row matched more than one source row, so Oracle could not decide which value to write.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-15

The classic message is unable to get a stable set of rows in the source tables. 23ai says it more directly: ORA-30926: The operation attempted to update the same row (rowid: '...') twice. The rowid is the target row that was hit twice.

In MERGE, each target row may be updated by at most one source row. If the ON condition matches two source rows to the same target row, the whole statement fails and nothing is changed. Look for duplicates on the join key in the source.

Causes and fixes

  1. The MERGE source has the same key more than once
  2. The ON clause uses only part of the target's key
  3. A correlated subquery in UPDATE SET reads several history rows

1The MERGE source has the same key more than once

The staging table price_stage had two rows for P1 (110 loaded at 09:00, 120 at 10:00). MERGE ... USING price_stage failed, with the * under the USING line.

GROUP BY product_id HAVING COUNT(*) > 1 found P1 with 2 rows. Keeping only the latest row per product with ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY loaded_at DESC) and WHERE rn = 1 gave 2 rows merged.: P1 = 120, P2 = 210.

Setup
Setuprun as lab_a
CREATE TABLE product (product_id VARCHAR2(10) PRIMARY KEY, price NUMBER);
CREATE TABLE price_stage (product_id VARCHAR2(10), price NUMBER, loaded_at DATE);
INSERT INTO product VALUES ('P1', 100);
INSERT INTO product VALUES ('P2', 200);
INSERT INTO price_stage VALUES ('P1', 110, TO_DATE('2026-11-15 09:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO price_stage VALUES ('P1', 120, TO_DATE('2026-11-15 10:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO price_stage VALUES ('P2', 210, TO_DATE('2026-11-15 09:00', 'YYYY-MM-DD HH24:MI'));
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
MERGE INTO product t
USING price_stage s
ON (t.product_id = s.product_id)
WHEN MATCHED THEN UPDATE SET t.price = s.price;
OUTPUT
USING price_stage s
      *
ERROR at line 2:
ORA-30926: The operation attempted to update the same row (rowid: 'AAASBxAAYAAAAAMAAA') twice.
Help: https://docs.oracle.com/error-help/db/ora-30926/
Checkrun as lab_a
SELECT product_id, COUNT(*) AS cnt FROM price_stage GROUP BY product_id HAVING COUNT(*) > 1;
OUTPUT
PRODUCT_ID        CNT
---------- ----------
P1                  2

1 row selected.

Decide which row should win (latest, largest, etc.) and make the source unique on the ON key.

After the fixrun as lab_a
MERGE INTO product t
USING (
  SELECT product_id, price
  FROM (SELECT product_id, price,
               ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY loaded_at DESC) AS rn
        FROM price_stage)
  WHERE rn = 1
) s
ON (t.product_id = s.product_id)
WHEN MATCHED THEN UPDATE SET t.price = s.price;
SELECT * FROM product ORDER BY product_id;
OUTPUT
2 rows merged.


PRODUCT_ID      PRICE
---------- ----------
P1                120
P2                210

2 rows selected.

2The ON clause uses only part of the target's key

monthly_sales has the primary key (yyyymm, store_id), but the MERGE joined only ON (t.store_id = s.store_id). Each S1 row in the target matched both feed rows (202610 and 202611), so the MERGE failed.

user_cons_columns showed the key columns YYYYMM and STORE_ID. With both in the ON clause, it gave 2 rows merged.: 202610 = 520, 202611 = 300.

Setup
Setuprun as lab_a
CREATE TABLE monthly_sales (yyyymm VARCHAR2(6), store_id VARCHAR2(5), amount NUMBER, PRIMARY KEY (yyyymm, store_id));
CREATE TABLE sales_feed (yyyymm VARCHAR2(6), store_id VARCHAR2(5), amount NUMBER);
INSERT INTO monthly_sales VALUES ('202610', 'S1', 500);
INSERT INTO monthly_sales VALUES ('202611', 'S1', 0);
INSERT INTO sales_feed VALUES ('202610', 'S1', 520);
INSERT INTO sales_feed VALUES ('202611', 'S1', 300);
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
MERGE INTO monthly_sales t
USING sales_feed s
ON (t.store_id = s.store_id)
WHEN MATCHED THEN UPDATE SET t.amount = s.amount;
OUTPUT
USING sales_feed s
      *
ERROR at line 2:
ORA-30926: The operation attempted to update the same row (rowid: 'AAASB0AAYAAAAAMAAA') twice.
Help: https://docs.oracle.com/error-help/db/ora-30926/
Checkrun as lab_a
SELECT constraint_name, column_name, position FROM user_cons_columns WHERE table_name = 'MONTHLY_SALES' ORDER BY position;
OUTPUT
CONSTRAINT_NAME                COLUMN_NAME                      POSITION
------------------------------ ------------------------------ ----------
SYS_C008941                    YYYYMM                                  1
SYS_C008941                    STORE_ID                                2

2 rows selected.
After the fixrun as lab_a
MERGE INTO monthly_sales t
USING sales_feed s
ON (t.yyyymm = s.yyyymm AND t.store_id = s.store_id)
WHEN MATCHED THEN UPDATE SET t.amount = s.amount;
SELECT * FROM monthly_sales ORDER BY yyyymm;
OUTPUT
2 rows merged.


YYYYMM STORE     AMOUNT
------ ----- ----------
202610 S1           520
202611 S1           300

2 rows selected.

3A correlated subquery in UPDATE SET reads several history rows

UPDATE emp e SET dept_name = (SELECT h.dept_name FROM dept_hist h WHERE h.dept_id = e.dept_id) failed with ORA-30926 in 23ai. The history table had two names for dept 10 (Sales from 2024, Sales Korea from 2026).

Picking the latest one with ORDER BY h.valid_from DESC FETCH FIRST 1 ROW ONLY gave 2 rows updated.: Sales Korea and IT.

Setup
Setuprun as lab_a
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, dept_id NUMBER, dept_name VARCHAR2(20));
CREATE TABLE dept_hist (dept_id NUMBER, dept_name VARCHAR2(20), valid_from DATE);
INSERT INTO emp VALUES (1, 10, NULL);
INSERT INTO emp VALUES (2, 20, NULL);
INSERT INTO dept_hist VALUES (10, 'Sales', DATE '2024-01-01');
INSERT INTO dept_hist VALUES (10, 'Sales Korea', DATE '2026-01-01');
INSERT INTO dept_hist VALUES (20, 'IT', DATE '2024-01-01');
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
UPDATE emp e
SET dept_name = (SELECT h.dept_name FROM dept_hist h WHERE h.dept_id = e.dept_id);
OUTPUT
UPDATE emp e
       *
ERROR at line 1:
ORA-30926: The operation attempted to update the same row (rowid: 'AAASB3AAYAAAAAMAAA') twice.
Help: https://docs.oracle.com/error-help/db/ora-30926/
Checkrun as lab_a
SELECT dept_id, dept_name, valid_from FROM dept_hist ORDER BY dept_id, valid_from;
OUTPUT
   DEPT_ID DEPT_NAME            VALID_FROM
---------- -------------------- ------------------
        10 Sales                01-JAN-24
        10 Sales Korea          01-JAN-26
        20 IT                   01-JAN-24

3 rows selected.

When the subquery has no link to the updated row, the same mistake gives ORA-01427 instead (see the ORA-01427 page).

After the fixrun as lab_a
UPDATE emp e
SET dept_name = (SELECT h.dept_name FROM dept_hist h
                 WHERE h.dept_id = e.dept_id
                 ORDER BY h.valid_from DESC
                 FETCH FIRST 1 ROW ONLY);
SELECT * FROM emp ORDER BY emp_id;
OUTPUT
2 rows updated.


    EMP_ID    DEPT_ID DEPT_NAME
---------- ---------- --------------------
         1         10 Sales Korea
         2         20 IT

2 rows selected.

FAQ

What does ORA-30926 unable to get a stable set of rows mean?

A MERGE (or UPDATE) tried to update the same target row more than once, because several source rows matched it. 23ai's message says this directly and shows the rowid.

How do I find the duplicate rows causing ORA-30926?

Group the source by the columns in the ON clause: SELECT key, COUNT(*) FROM source GROUP BY key HAVING COUNT(*) > 1;

How do I fix ORA-30926 in MERGE?

Make the source unique on the ON key, e.g. keep the latest row with ROW_NUMBER(), or include all key columns of the target in the ON clause.

Did the MERGE update some rows before ORA-30926?

No. The statement fails as a whole; in these tests the rows kept their old values until the fixed MERGE ran.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors