Update a referenced column in a MERGE statement

Here's how to update a referenced column in a MERGE statement.

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).


This was first published in March 2005
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close