Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Problem With Merge Statement

Problem With Merge Statement

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 5 Apr 2006 15:08:30 -0700
Message-ID: <1144274910.210123.74410@t31g2000cwb.googlegroups.com>


Greetings,

I have two table's as

category

Name exp_date exp_time Flg

Tom     23-03-06       23:00:00       N
dick     23-03-06       23:00:00       Y
tom     23-03-06        23:00:00        Y
harry   24-03-06        23:00:00        Y
Tom    24-03-06        23:00:00        N
harry   24-03-06        23:00:00        Y

and

ex_category

Name exp_date exp_time Flg

Tom                                        N
dick                                        Y
tom    23-03-06     23:00:00       Y
harry   		              Y
Tom    24-03-06    23:00:00       N
harry  24-03-06     23:00:00        Y

I want to merge records from category into ex_category using Condition: ex_category.name = category.name

	     and ex_category.exp_date IS NULL
	     and ex_category.exp_time IS NULL

I did the following:

MERGE INTO ext_category t_dest
USING ( SELECT * FROM category) t_src
ON (t_dest.name = t_src.name

    and t_dest.exp_date IS NULL
    and t_dest.exp_time IS NULL
   )
WHEN MATCHED THEN
        UPDATE SET t_dest.exp_date=t_src.exp_date, t_dest.exp_time=t_src.exp_time
WHEN NOT MATCHED THEN

	INSERT(name, exp_date, exp_time, flg)
            VALUES(t_src.name, t_src.exp_date, t_exp_time, t_src.flg);

But its giving me the error that I cannot update the columns referenced

in ON clause.

Can anyone please tell me a workaround. I'll be happy if it's still possible to
do it using MERGE statement or any other workaround?

TIA Received on Wed Apr 05 2006 - 17:08:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US