ORACLE 10g - Merge Into statement

In the traditional programming world, when we try to perform a function of update, we will check if the matching record exists in the system. If so, we would update the record. If the matching record does not available in the system, we would insert the row.

ORACLE's merge statment performs the update/insert through a single statment. Through the merge statement, the validation or check can be made before update/insert. For eg: The following merge statement will update the table EASTable when the userid matches with the input. If the user id is not present in the table, the input user information will be inserted.

MERGE INTO EASTable EAS
USING DUAL
ON ( EAS.USER_ID = USER_ID_INPUT)
WHEN MATCHED THEN
UPDATE SET EAS.IND1 = IND1_INPUT
EAS.IND2 = IND2_INPUT

WHEN NOT MATCHED THEN
INSERT
(EAS.USER_ID, EAS.IND1,EAS.IND2)
VALUES (USER_ID_INPUT,IND1_INPUT,IND2_INPUT);
COMMIT;