Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Problem With Merge Statement
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