Blog Oracle PL/SQL

oracle merge command

Oracle MERGE command was added in 9i but I only learned about it lately. This command is sometimes referred to as UPSERT because in one statement, you can both update and insert records into a target. Based on the matching condition, MERGE can update existing records if a match is found or insert new record if not. Prior to 9i, we need separate sql statements or a PL/SQL program to do this.



MERGE INTO tstg_idl_approach a
USING tmdb_idl_approach b
ON (a.approach_id = b.approach_id)
WHEN MATCHED THEN
UPDATE SET a.approach_code = b.approach_code ,
a.current_ind = b.current_ind
WHEN NOT MATCHED THEN
INSERT (approach_id ,
approach_code,
current_ind )
VALUES (b.approach_id ,
b.approach_code,
b.current_ind );

For further reading, I recommend visiting http://www.oracle-developer.net/display.php?id=203.