2

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.

2
  • If the data in the non-key columns is different, which rows should you keep/use? The one in the smaller table or the one in the bigger table? Is there any logic that defines which should be kept?
    – Philᵀᴹ
    Commented Dec 28, 2015 at 8:32
  • If the data in the non-key columns is different then we need the data from the smaller table
    – alonk
    Commented Dec 28, 2015 at 8:42

1 Answer 1

4

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)
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.