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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 11 Dec 2004 07:04:35 +0100
Message-ID: <41ba8cfc$0$12712$626a14ce@news.free.fr>

<chirantan.chakraborty_at_gmail.com> a écrit dans le message de news:1102724333.989213.186730_at_f14g2000cwb.googlegroups.com...
> 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
>

If service_type_cde is the primary key and not service_type_dim_key:

update (select a.service_type_dim_key, a.service_type_cde, b.service_type_dim_key, b.service_type_cde

            from service_type_dim a, service_type_dim_bk b where a.service_type_cde=b.service_type_cde)
set a.service_type_dim_key=b.service_type_dim_key;

else:

update service_type_dim a
set service_type_dim_key =

     (select service_type_dim_key from service_type_dim_bk b
      where a.service_type_cde=b.service_type_cde)

where exists (select 1 from service_type_dim_bk b where a.service_type_cde=b.service_type_cde);

Regards
Michel Cadot Received on Sat Dec 11 2004 - 00:04:35 CST