ORA-30926 means a MERGE, or an update through a join, would change the same target row more than once, because several source rows match it. Oracle cannot know which change should win, so it stops. In Oracle AI Database 26ai the message says it directly: the operation attempted to update the same row twice, and gives its ROWID. The fix is to make the source return one row per target row.
Reproduce the Error
The feed has two rows for SKU A1, and both match the one stock row:
Example:
create table demo_stock (sku varchar2(10) primary key, qty number);
create table demo_stock_feed (sku varchar2(10), qty number);
insert into demo_stock values ('A1', 5);
insert into demo_stock_feed values ('A1', 7);
insert into demo_stock_feed values ('A1', 9); -- the same SKU twice
commit;
merge into demo_stock s
using demo_stock_feed f
on (s.sku = f.sku)
when matched then update set s.qty = f.qty;Output:
Table DEMO_STOCK created. Table DEMO_STOCK_FEED created. 1 row inserted. 1 row inserted. 1 row inserted. Commit complete. Error starting at line : 8 In command - merge into demo_stock s using demo_stock_feed f on (s.sku = f.sku) when matched then update set s.qty = f.qty Error at Command Line : 9 Column : 7 Error report - SQL Error: ORA-30926: The operation attempted to update the same row (rowid: 'AAAYCuAAAAAASCzAAA') twice.
Fix: One Source Row per Target Row
Decide which value wins and reduce the source to it, here with GROUP BY and MAX:
Example:
-- one source row per target row: decide which value wins merge into demo_stock s using (select sku, max(qty) as qty from demo_stock_feed group by sku) f on (s.sku = f.sku) when matched then update set s.qty = f.qty; select * from demo_stock;
Output:
1 row merged. SKU QTY ______ ______ A1 9
Other Ways to Pick the Row
- The latest row by a timestamp: ROW_NUMBER() OVER (PARTITION BY sku ORDER BY loaded_at DESC) and keep row 1.
- A sum of all source rows, when they are increments: SUM(qty) with GROUP BY.
- Find the duplicates first: GROUP BY the join key HAVING COUNT(*) > 1 on the source.
Things to Know
- The ON condition must identify at most one source row per target row; check it against the source's real keys.
- Updating a join view so that one row gets two different values raises ORA-30926 as well.
Related Guides
- Using MERGE Statement in Oracle Database 23ai
- How to Fix ORA-00001: Unique Constraint Violated in Oracle
Conclusion
ORA-30926 means several source rows matched one target row in a MERGE or join update. Find the duplicate keys in the source, then reduce it to one row per key with GROUP BY or ROW_NUMBER before merging.
