Re: How to use merge not to do an upsert but to do a simple update

chirantan.chakraborty_at_gmail.com wrote:
> Hi All,
>
> Although the main purpose of using MERGE in Oracle 9i is to do
UPSERT,
> but I somehow want to find out if it is at all possible for us to do
a
> plain UPDATE with it instead.
>
> I know I can use a CURSOR to do this, but I would like to experiment
> with MERGE.
> ==============================================================
> MERGE INTO SERVICE_TYPE_DIM A
> USING SERVICE_TYPE_DIM_BK B
> on
> (
> A.SERVICE_TYPE_CDE = B.SERVICE_TYPE_CDE
> )
> WHEN MATCHED THEN UPDATE
> SET
> A.SERVICE_TYPE_DIM_KEY = B.SERVICE_TYPE_DIM_KEY
> WHEN NOT MATCHED THEN
> -- do not want to insert anything
> INSERT()
> VALUES
> (
> )
> ==============================================================
>
> Please let me know if there is a way to not use "WHEN NOT MATCHED
THEN"
> part. Any response would be appreciated.
>
> Thanks,
>
> -- Chirantan Chakraborty
> Business Intelligence,
> Hewlett-Packard Services

I believe the only purpose of MERGE INTO is to perform 'upserts'. There is no provision to eliminate any of the required keywords, meaning you cannot dispose of WHEN NOT MATCHED THEN or anything that comes after it. What has givne rise to this desire to use the MERGE INTO functionality for something other than its intended purpose? A simple UPDATE would perform the desired task; trying to mangle MERGE INTO to do something it was never intended to do is, in my opinion, folly.

Use a simple update statement since that's all you want to do. Stop this nonsense of trying to make MERGE INTO something it is not. David Fitzjarrell Received on Fri Dec 10 2004 - 22:31:24 CST