Merge Statement on Oracle database

ISSUE:

  Merge statement failed on "ORA-38104: Columns referenced in the ON Clause cannot be updated;" when update a common column of both target and source table.

CAUSE:
 The updated common column is included in ON clause of merge condition.

SOLUTION:
Remove ON clause condition including the updated column and put the condition as where clause appended to update statement

EXAMPLE:

Failed statement:

MERGE INTO TEST1 target USING
      (SELECT * FROM TEST2 WHERE created = inserted ) source
      ON (target.ID = source.Id AND target.secondId is null AND target.created = source.created)
    WHEN MATCHED THEN
       UPDATE SET target.secondId = 'secondid value';

ORA-38104: Columns referenced in the ON Clause cannot be updated: "TARGET"."SECONDID"

Succeeded statement:

MERGE INTO TEST1 target USING
      (SELECT * FROM TEST2 WHERE created = inserted ) source
      ON (target.ID = source.Id AND target.created = source.created)
    WHEN MATCHED THEN
       UPDATE SET target.secondId = 'secondid value' where target.secondId is null ;

MERGE STATEMENT SYNTEX:

SQL>      MERGE
                                INTO dbo.TargetTable tgt    -- Target Table
                                USING dbo.SourceTable src   -- Source Table
                                ON tgt.ID = src.ID          -- Main comparison criteria
                WHEN MATCHED                                 -- When ID's exist in both tables
                                THEN
                                -- DO SOMETHING
                WHEN NOT MATCHED                    -- When ID's from Source do not exist in Target
                                THEN
                                -- DO SOMETHING
                WHEN NOT MATCHED BY SOURCE          -- When ID's from Target do not exist in Source
                                THEN
                                -- DO SOMETHING

REFERENCE:

http://www.c-sharpcorner.com/UploadFile/0e9eba/oracle-merge-statement-%E2%80%93-quick-catch-up/

http://stackoverflow.com/questions/10600120/oracle-merge-statement-with-conditional-insert

http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_14.shtml

http://psoug.org/reference/merge.html

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm