Tip

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.