One of the most powerful features in Oracle 9i and above is the MERGE statement, which lets a single SQL statement either conditionally insert or update a table by selecting rows from another table. If the row already exists, an update is performed; otherwise, an insert is performed. However, there is one restriction with MERGE statements: you cannot
update a column that has been referenced in the ON condition
clause. This tip will help you cope with this sort of situation.
For example, let's assume that we have two tables, "category" and "ETL_category":
SQL> desc category
Name Type
---- ------------
id Number
description Varchar2(25)
exp_date Date
SQL> desc etl_category
Name Type
---- ------------
id Number
catg_name Varchar2(25)
exp_date Date
Then, update the exp_date column of the table "category" with the exp_date column of the "etl_category" table if records exist in "etl_catgeory" for the
corresponding ID, otherwise insert a record into the "category" table:
MERGE into category A using etl_category B ON (a.id = b.id)
WHEN MATCHED THEN UPDATE set a.expdate = b.expdate
WHEN NOT MATCHED THEN INSERT (id,description, exp_date) VALUES
(b.id,b.catg_name,b.exp_date);
Now, let's assume that there is a condition on exp_date also; e.g., only update those records if exp_date of "category" < exp_date of "etl_category" for the matching ID's. That may seem simiple, but if you try the following:
MERGE into category A using etl_category B ON (a.id = b.id AND a.expdate < b.expdate)
WHEN MATCHED THEN UPDATE set a.expdate = b.expdate
WHEN NOT MATCHED THEN INSERT (id,description, exp_date) VALUES
(b.id,b.catg_name,b.exp_date);
You get...
ERROR at line 2: ORA-00904: "A"."EXP_DATE": invalid identifier
The reason you see the ORA-00904 is not because of any syntax error; it's
because you cannot update a column that has been referenced in the ON
condition.
The solution to the problem is to use the SIGN and DECODE functions in the MERGE statement:
MERGE into category A using etl_category B on (a.id=b.id )
WHEN MATCHED THEN UPDATE set a.exp_date = decode ( sign((a.exp_date -
b.exp_date )),-1,b.exp_date,a.exp_date)
WHEN NOT MATCHED THEN INSERT (id,description,exp_date) values
(b.id,b.catg_name,b.exp_date);
The SIGN function returns either -1 (if the supplied number is less than
zero), 0 (if the number is zero) or 1 (if the number is greater than zero).
