Re: Oracle Merge Statement -sample query
Date: Thu, 22 May 2008 07:12:03 -0700 (PDT)
Message-ID: <380366d2-4256-4b7e-989c-75df3edebc39@a70g2000hsh.googlegroups.com>
On 22 Maijs, 10:25, idey <dey.indra..._at_gmail.com> wrote:
> The problem with Merge I think is that its trying to merge from a
> source table/ view/ dataset into the taget table. My problem is when I
> have a single table to work on i.e. source and target tables are same.
> In this case the predicate inside the using clause filters all records
> so no rows are returned.
Source and target tables are not the same. You have one fixed row and
it could and couldn't be in target table, so it cannot be the source
table. So you can use subquery from dual as your source e.g.
MERGE INTO employee e
USING (SELECT <necessary number> id FROM dual) s
ON (s.id = e.id)
WHEN MATCHED THEN UPDATE SET <col> = <whatever>
WHEN NOT MATCHED THEN INSERT VALUES (id, default, default);
BTW in your example you are not using default values or at least your meaning of "default" is not the same meaning as "default" values for columns in Oracle.
Gints Plivna
http://www.gplivna.eu
Received on Thu May 22 2008 - 09:12:03 CDT