ORA-30926: Unable to Get a Stable Set of Rows - Fix MERGE Duplicates

ORA-30926: Unable to Get a Stable Set of Rows in the Source Tables

Error Overview

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

This error occurs during a MERGE statement when the source query returns more than one row that matches a single row in the target table via the ON clause. Oracle cannot determine which source row to use for the update or insert, so it raises this error to prevent non-deterministic results. The “stable set of rows” means each target row must match at most one source row.

Understanding the Problem

How MERGE Works

MERGE INTO target
USING source
ON (target.key = source.key)     <-- Each target row must match 0 or 1 source rows
WHEN MATCHED THEN UPDATE ...     <-- Which source row should Oracle use if there are 2?
WHEN NOT MATCHED THEN INSERT ...

If source has duplicate keys:
  Target Row (key=100)  <-->  Source Row 1 (key=100, val='A')
                        <-->  Source Row 2 (key=100, val='B')
  = ORA-30926: Which value should Oracle use? 'A' or 'B'?

Common Scenarios

• Duplicate rows in source table or subquery — The source data has multiple rows with the same join key
• Insufficient join conditions in the ON clause — Join is too broad, matching multiple source rows to one target
• Missing DISTINCT in source query — Joins in the USING clause produce row multiplication
• Non-deterministic subqueries — Correlated subqueries in the source returning multiple rows
• Data quality issues — Unexpected duplicates in staging or ETL source tables
• Cartesian products in USING clause — Missing join conditions within the source query itself

Diagnostic Steps

1. Identify Duplicate Rows in the Source

-- Extract the source query from your MERGE and check for duplicates
-- on the columns used in the ON clause

-- Example: If your MERGE ON clause is:
-- ON (target.employee_id = source.employee_id)

-- Check for duplicates in the source query:
SELECT
  employee_id,        -- Column(s) from the ON clause
  COUNT(*) as row_count
FROM (
  -- Paste your USING subquery here
  SELECT employee_id, salary, department_id
  FROM staging_employees
) source
GROUP BY employee_id
HAVING COUNT(*) > 1
ORDER BY row_count DESC;

2. Find the Actual Duplicate Data

-- Once you know which keys are duplicated, examine the rows
SELECT *
FROM (
  -- Your USING source query here
  SELECT employee_id, salary, department_id
  FROM staging_employees
) source
WHERE employee_id IN (
  SELECT employee_id
  FROM staging_employees
  GROUP BY employee_id
  HAVING COUNT(*) > 1
)
ORDER BY employee_id;

-- For a source query with joins, check if the join creates duplicates
-- Example: staging table joined to a lookup
SELECT
  s.employee_id,
  s.salary,
  d.department_name,
  COUNT(*) OVER (PARTITION BY s.employee_id) as duplicate_count
FROM staging_employees s
JOIN departments d ON s.department_id = d.department_id
ORDER BY duplicate_count DESC, s.employee_id;

3. Analyze the ON Clause Join Conditions

-- Verify that the ON clause columns form a unique key in the source
-- Check if additional columns should be in the ON clause

-- Example: Check uniqueness of join columns in the source
SELECT
  'Source rows: ' || COUNT(*) as total_rows,
  'Distinct keys: ' || COUNT(DISTINCT employee_id) as distinct_keys,
  'Duplicate keys: ' || (COUNT(*) - COUNT(DISTINCT employee_id)) as duplicate_keys
FROM staging_employees;

-- For composite keys, check the combination
SELECT
  'Source rows: ' || COUNT(*) as total_rows,
  'Distinct keys: ' || COUNT(DISTINCT employee_id || '-' || effective_date) as distinct_keys
FROM staging_employees;

-- Find which target rows would match multiple source rows
SELECT
  t.employee_id,
  COUNT(s.employee_id) as matching_source_rows
FROM employees t
JOIN staging_employees s ON t.employee_id = s.employee_id  -- Same as your ON clause
GROUP BY t.employee_id
HAVING COUNT(s.employee_id) > 1
ORDER BY matching_source_rows DESC;

4. Validate Before Running the MERGE

-- Pre-flight check: run this before any MERGE to verify source uniqueness
-- Replace the join columns with your actual ON clause columns

WITH source_data AS (
  -- Paste your USING clause query here
  SELECT employee_id, salary, department_id
  FROM staging_employees
),
duplicate_check AS (
  SELECT
    employee_id,  -- ON clause column(s)
    COUNT(*) as cnt
  FROM source_data
  GROUP BY employee_id
  HAVING COUNT(*) > 1
)
SELECT
  CASE
    WHEN COUNT(*) = 0 THEN 'SAFE: No duplicates found - MERGE will succeed'
    ELSE 'UNSAFE: ' || COUNT(*) || ' duplicate key(s) found - MERGE will fail with ORA-30926'
  END as merge_status,
  COUNT(*) as duplicate_key_count,
  NVL(SUM(cnt), 0) as total_duplicate_rows
FROM duplicate_check;

Immediate Solutions

Solution 1: Add DISTINCT to the Source Query

-- Before (causes ORA-30926):
MERGE INTO employees target
USING (
  SELECT employee_id, salary, department_id
  FROM staging_employees
) source
ON (target.employee_id = source.employee_id)
WHEN MATCHED THEN
  UPDATE SET target.salary = source.salary;

-- After (add DISTINCT if rows are true duplicates):
MERGE INTO employees target
USING (
  SELECT DISTINCT employee_id, salary, department_id
  FROM staging_employees
) source
ON (target.employee_id = source.employee_id)
WHEN MATCHED THEN
  UPDATE SET target.salary = source.salary;

Solution 2: Use ROW_NUMBER() to Pick One Row Per Key

-- When duplicates have different values and you need to choose one
-- (e.g., keep the most recent record)
MERGE INTO employees target
USING (
  SELECT employee_id, salary, department_id
  FROM (
    SELECT
      employee_id,
      salary,
      department_id,
      ROW_NUMBER() OVER (
        PARTITION BY employee_id
        ORDER BY last_modified DESC  -- Keep most recent
      ) as rn
    FROM staging_employees
  )
  WHERE rn = 1
) source
ON (target.employee_id = source.employee_id)
WHEN MATCHED THEN
  UPDATE SET target.salary = source.salary,
             target.department_id = source.department_id
WHEN NOT MATCHED THEN
  INSERT (employee_id, salary, department_id)
  VALUES (source.employee_id, source.salary, source.department_id);

Solution 3: Fix the ON Clause Join Conditions

-- Before (ON clause too broad - matches multiple source rows):
MERGE INTO order_items target
USING staging_order_items source
ON (target.order_id = source.order_id)  -- Multiple items per order!
WHEN MATCHED THEN
  UPDATE SET target.quantity = source.quantity;

-- After (add additional join columns for uniqueness):
MERGE INTO order_items target
USING staging_order_items source
ON (target.order_id = source.order_id
    AND target.product_id = source.product_id)  -- Now unique per row
WHEN MATCHED THEN
  UPDATE SET target.quantity = source.quantity;

Solution 4: Deduplicate the Source Table First

-- Remove duplicates from the staging table before the MERGE
-- Step 1: Identify duplicates
SELECT employee_id, COUNT(*) as cnt
FROM staging_employees
GROUP BY employee_id
HAVING COUNT(*) > 1;

-- Step 2: Delete duplicates, keeping the row with the latest data
DELETE FROM staging_employees
WHERE ROWID NOT IN (
  SELECT MIN(ROWID)
  FROM (
    SELECT
      ROWID as rid,
      ROW_NUMBER() OVER (
        PARTITION BY employee_id
        ORDER BY last_modified DESC NULLS LAST
      ) as rn
    FROM staging_employees
  )
  WHERE rn = 1
);

COMMIT;

-- Step 3: Verify no duplicates remain
SELECT employee_id, COUNT(*)
FROM staging_employees
GROUP BY employee_id
HAVING COUNT(*) > 1;

-- Step 4: Now run the MERGE safely
MERGE INTO employees target
USING staging_employees source
ON (target.employee_id = source.employee_id)
WHEN MATCHED THEN
  UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN
  INSERT (employee_id, salary) VALUES (source.employee_id, source.salary);

Solution 5: Use Aggregation Instead of Picking a Row

-- When you want to combine duplicate source rows (e.g., sum quantities)
MERGE INTO inventory target
USING (
  SELECT
    product_id,
    SUM(quantity) as total_quantity,
    MAX(last_updated) as last_updated
  FROM staging_inventory
  GROUP BY product_id
) source
ON (target.product_id = source.product_id)
WHEN MATCHED THEN
  UPDATE SET
    target.quantity = target.quantity + source.total_quantity,
    target.last_updated = source.last_updated
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity, last_updated)
  VALUES (source.product_id, source.total_quantity, source.last_updated);

Long-Term Solutions

1. Add Unique Constraints to Staging Tables

-- Prevent duplicates from entering the staging table in the first place
ALTER TABLE staging_employees
ADD CONSTRAINT uk_staging_emp_id UNIQUE (employee_id);

-- For ETL processes where duplicates might arrive, use error logging
-- to capture rejected rows instead of failing the whole load
BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG(
    dml_table_name => 'STAGING_EMPLOYEES',
    err_log_table_name => 'ERR_STAGING_EMPLOYEES'
  );
END;
/

-- Load with error logging (duplicates go to error table)
INSERT INTO staging_employees (employee_id, salary, department_id)
SELECT employee_id, salary, department_id
FROM external_data_source
LOG ERRORS INTO err_staging_employees ('Load batch 2024-01-15')
REJECT LIMIT UNLIMITED;

-- Check what was rejected
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$, employee_id
FROM err_staging_employees
ORDER BY ora_err_number$;

2. Create a Reusable MERGE Wrapper

-- Procedure that validates source uniqueness before executing MERGE
CREATE OR REPLACE PROCEDURE safe_merge_employees(
  p_source_query IN VARCHAR2,
  p_key_columns  IN VARCHAR2,  -- Comma-separated ON clause columns
  p_dry_run      IN BOOLEAN DEFAULT FALSE
) AS
  v_dup_count NUMBER;
  v_sql       VARCHAR2(4000);
BEGIN
  -- Check for duplicates on the join key
  v_sql := 'SELECT COUNT(*) FROM (' ||
           '  SELECT ' || p_key_columns || ', COUNT(*) as cnt' ||
           '  FROM (' || p_source_query || ')' ||
           '  GROUP BY ' || p_key_columns ||
           '  HAVING COUNT(*) > 1' ||
           ')';

  EXECUTE IMMEDIATE v_sql INTO v_dup_count;

  IF v_dup_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: ' || v_dup_count ||
      ' duplicate key(s) found in source data.');
    DBMS_OUTPUT.PUT_LINE('MERGE would fail with ORA-30926.');
    DBMS_OUTPUT.PUT_LINE('Run duplicate diagnostic query to investigate.');
    RAISE_APPLICATION_ERROR(-20050,
      'MERGE aborted: ' || v_dup_count || ' duplicate keys in source on (' ||
      p_key_columns || ')');
  END IF;

  IF p_dry_run THEN
    DBMS_OUTPUT.PUT_LINE('DRY RUN: Source data is clean. ' ||
      'MERGE would proceed successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Source data verified. Proceeding with MERGE...');
    -- Execute the actual MERGE here
  END IF;
END;
/

3. ETL Best Practices for MERGE Operations

-- Standard ETL pattern: Stage, Validate, Merge

-- Step 1: Load to staging (allow duplicates)
-- (Data Pump, SQL*Loader, external tables, etc.)

-- Step 2: Create a clean view with deduplication built in
CREATE OR REPLACE VIEW v_staging_employees_clean AS
SELECT employee_id, salary, department_id, hire_date
FROM (
  SELECT
    employee_id,
    salary,
    department_id,
    hire_date,
    ROW_NUMBER() OVER (
      PARTITION BY employee_id
      ORDER BY load_timestamp DESC  -- Most recent wins
    ) as rn
  FROM staging_employees
)
WHERE rn = 1;

-- Step 3: MERGE from the clean view (guaranteed no duplicates)
MERGE INTO employees target
USING v_staging_employees_clean source
ON (target.employee_id = source.employee_id)
WHEN MATCHED THEN
  UPDATE SET
    target.salary = source.salary,
    target.department_id = source.department_id,
    target.last_updated = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (employee_id, salary, department_id, hire_date, last_updated)
  VALUES (source.employee_id, source.salary, source.department_id,
          source.hire_date, SYSDATE);

-- Step 4: Truncate staging after successful merge
TRUNCATE TABLE staging_employees;

Oracle 23ai Enhancements

Oracle 23ai significantly improves the diagnosis of ORA-30926:

-- In Oracle 23ai, the error message now includes the ROWID of the
-- problematic target row, making diagnosis much faster:
--
-- ORA-30926: unable to get a stable set of rows in the source tables
-- ROWID: AAASfPAAEAAAAJXAAA
--
-- Use the ROWID to immediately find the problem row:
SELECT *
FROM target_table
WHERE ROWID = 'AAASfPAAEAAAAJXAAA';  -- ROWID from the error message

-- Then find all source rows matching that target row:
SELECT s.*
FROM source_query s
JOIN target_table t ON s.join_key = t.join_key  -- Your ON clause
WHERE t.ROWID = 'AAASfPAAEAAAAJXAAA';

-- Oracle 23ai also introduces ORA-14359 for DML restart scenarios.
-- When a MERGE triggers a partition maintenance operation that
-- requires DML restart, and the restarted operation encounters
-- the same duplicate-key issue, ORA-14359 is raised instead.
-- The diagnosis and resolution steps are the same as for ORA-30926.

Prevention Strategies

1. Always Verify Source Uniqueness Before MERGE

-- Add this check as a standard step before any MERGE in production
-- Create a reusable function
CREATE OR REPLACE FUNCTION check_source_unique(
  p_source_sql  IN VARCHAR2,
  p_key_columns IN VARCHAR2
) RETURN VARCHAR2 AS
  v_count NUMBER;
  v_sql   VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM (' ||
           'SELECT ' || p_key_columns ||
           ' FROM (' || p_source_sql || ')' ||
           ' GROUP BY ' || p_key_columns ||
           ' HAVING COUNT(*) > 1)';

  EXECUTE IMMEDIATE v_sql INTO v_count;

  IF v_count = 0 THEN
    RETURN 'OK: Source is unique on (' || p_key_columns || ')';
  ELSE
    RETURN 'FAIL: ' || v_count || ' duplicate key(s) on (' || p_key_columns || ')';
  END IF;
END;
/

2. Test with SELECT Before MERGE

-- Convert your MERGE to a diagnostic SELECT to preview what would happen
-- This shows which target rows match multiple source rows

-- Instead of running the MERGE, first run:
SELECT
  t.employee_id as target_key,
  COUNT(s.employee_id) as source_matches,
  CASE
    WHEN COUNT(s.employee_id) = 0 THEN 'INSERT (new row)'
    WHEN COUNT(s.employee_id) = 1 THEN 'UPDATE (safe)'
    ELSE 'ORA-30926 (duplicate source rows!)'
  END as merge_action
FROM employees t
FULL OUTER JOIN staging_employees s
  ON t.employee_id = s.employee_id
GROUP BY t.employee_id
HAVING COUNT(s.employee_id) > 1  -- Show only problem rows
ORDER BY source_matches DESC;

3. Design Patterns for Safe MERGE

-- Pattern 1: Always deduplicate inline
MERGE INTO target t
USING (
  SELECT * FROM (
    SELECT src.*, ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY priority_col DESC) rn
    FROM source src
  ) WHERE rn = 1
) s ON (t.key_col = s.key_col)
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (key_col, val) VALUES (s.key_col, s.val);

-- Pattern 2: Use a unique staging table
-- Create staging with a unique constraint matching your ON clause
-- Load data with DML error logging to catch duplicates before MERGE

-- Pattern 3: Two-step approach for complex transformations
-- Step 1: INSERT new rows
INSERT INTO target (key_col, val)
SELECT s.key_col, s.val
FROM source s
WHERE NOT EXISTS (SELECT 1 FROM target t WHERE t.key_col = s.key_col);

-- Step 2: UPDATE existing rows
UPDATE target t
SET val = (
  SELECT s.val FROM (
    SELECT key_col, val, ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY modified DESC) rn
    FROM source
  ) s WHERE s.key_col = t.key_col AND s.rn = 1
)
WHERE EXISTS (SELECT 1 FROM source s WHERE s.key_col = t.key_col);

Diagnostic Scripts

These Oracle Day by Day scripts can help diagnose and resolve this error:

• gvsess.sql — Active session monitoring
• gvlock.sql — Lock analysis
• dinvalid.sql — Find invalid database objects

Related Errors

• ORA-00001 - Unique constraint violated (related duplicate data issue)
• ORA-01427 - Single-row subquery returns more than one row (similar concept)
• ORA-01422 - Exact fetch returns more than requested number of rows
• ORA-00060 - Deadlock detected (can occur during concurrent MERGE operations)

Quick Reference

Emergency Response Steps

1. Identify the MERGE statement that failed
2. Extract the USING source query and run it standalone
3. Check for duplicate keys on the ON clause columns using GROUP BY / HAVING COUNT(*) > 1
4. Examine the duplicate rows to decide: DISTINCT, ROW_NUMBER(), aggregate, or fix the data
5. Fix the source query or deduplicate the staging data
6. On Oracle 23ai, use the ROWID from the error message to locate the problem instantly
7. Re-run the MERGE

Quick Commands

-- Find duplicates in source on the join key
SELECT join_key, COUNT(*) FROM source_table
GROUP BY join_key HAVING COUNT(*) > 1;

-- Deduplicate with ROW_NUMBER (keep latest)
SELECT * FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY mod_date DESC) rn
  FROM source_table t
) WHERE rn = 1;

-- Preview MERGE matches
SELECT t.key_col, COUNT(s.key_col) as matches
FROM target t JOIN source s ON t.key_col = s.key_col
GROUP BY t.key_col HAVING COUNT(s.key_col) > 1;

-- Quick fix: add DISTINCT to USING clause
MERGE INTO target USING (SELECT DISTINCT * FROM source) s
ON (target.key = s.key) WHEN MATCHED THEN UPDATE SET ...;

-- Oracle 23ai: Use ROWID from error message
SELECT * FROM target WHERE ROWID = 'ROWID_FROM_ERROR';

Prevention Guidelines

• Always verify source uniqueness before running MERGE statements
• Use ROW_NUMBER() to deterministically pick one row per key
• Add unique constraints to staging tables matching the MERGE ON clause
• Test with SELECT first — preview the join results before executing
• Design ETL with clean views — deduplicate at the view level
• Use DML error logging — capture rejected duplicates instead of failing
• Document ON clause requirements — specify which columns must be unique in source