How to merge 2 tables in Oracle

mergeoracleupdate

I have 1 big table with 25 milions of rows and i have to merge data of another table with 500,000 rows into the big table. the rows of both tables have the same columns and i have to check if the key columns exist then i update the rest of the columns , otherwise i insert the new row.
i would like to know which is the best way to do the update:
1) update the columns according to the key columns
2) delete the old column and insert the new one instead.

Best Answer

You can fully achieve this with MERGE:

MERGE INTO big_table B
USING (SELECT key_col, col1, col2 FROM small_table) S
ON (B.key_col = S.key_col)
WHEN MATCHED THEN UPDATE SET B.col1=S.col1, B.col2=S.col2
WHEN NOT MATCHED THEN INSERT (B.col1, B.col2)
VALUES (S.col2, S.col2)
/

Related Solutions

Oracle – Update Row Where Associated Row Does Not Exist

Add the predicate:

 AND NOT EXISTS (
 SELECT 1 FROM role AS r_existing 
 WHERE r_existing.role_name = 'prefix-' || role.role_name)

SQL Server Update – How to Avoid Repeating the Same Condition in a WHEN MATCHED THEN UPDATE Clause

MERGE is a handy statement, but limited in situations such as this. I'd suggest simply breaking apart your query so that you can completely cover your logic. If you were expecting or required the fact that the MERGE statement will perform it's operations in a single transaction, you can declare an explicit transaction around your logic:

BEGIN TRAN
INSERT
WHERE

UPDATE
WHERE

UPDATE
WHERE

COMMIT TRAN

Best Answer

Related Solutions

Oracle – Update Row Where Associated Row Does Not Exist

SQL Server Update – How to Avoid Repeating the Same Condition in a WHEN MATCHED THEN UPDATE Clause

Related Question