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:
Post a Comment