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 -> Re: Problem With Merge Statement

Re: Problem With Merge Statement

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 06 Apr 2006 09:14:27 -0700
Message-ID: <1144340064.656706@yasure.drizzle.com>


pankaj_wolfhunter_at_yahoo.co.in wrote:
> 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
Never post about a problem without identifying the version number and providing the complete actual error message.

That said take Sybrand's advice and look up the syntax and examples in Morgan's Library at www.psoug.org under Merge.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Apr 06 2006 - 11:14:27 CDT

Original text of this message

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