Total Pageviews

2014/02/26

Oracle: how to UPSERT (Update or Insert)

Assume we have a requirement to read text file which sent by external system, and the UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data.

Oracle 9i introduced the MERGE statement. The MERGE statement takes a list of records which are usually in a staging table, and adds them to a master table. If the record exists in the master table, it should be updated with the new values in the staging table, otherwise insert the record from the staging table.

MERGE statement doesn't always need "multiple tables", but it does need a query as the source. Something like this should work:

MERGE INTO FMS420FB T1 USING DUAL ON (T1.YEAR = :YEAR
                                      AND T1.MONTH= :MONTH
                                      AND T1.SUBJECT_ID=:SUBJECT_ID) WHEN MATCHED THEN
UPDATE
SET T1.SUBJECT=:SUBJECT,
               T1.AMT=:AMT WHEN NOT MATCHED THEN
INSERT (T1.YEAR,
        T1.MONTH,
        T1.SUBJECT_ID,
        T1.SUBJECT,
        T1.AMT)
VALUES(:YEAR,
       :MONTH,
       :SUBJECT_ID,
       :SUBJECT,
       :AMT)

No comments: