Re: Merge doesn't do delete

  • From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
  • To: kennaim@xxxxxxxxx
  • Date: Sat, 14 Oct 2006 09:25:03 +0200

Yes, but only on the rows that were updated by the merge itself it will do a
delete. Rows that didn't match the join condition won't be deleted. Check
the sql reference for more infos

Stefan

On 10/13/06, Ken Naim <kennaim@xxxxxxxxx> wrote:

In 10g it does do deletes. http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Yavor Ivanov
Sent: Friday, October 13, 2006 9:06 AM
To: John_J_Cantu@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Merge doesn't do delete

        The purpose of the 'Merge' command is not to sync two tables. It's
purpose is to "upsert" new data to a table containing old data. Best for
ETL
in staging area of DWH. Like if you have lots of data in some dimension
table, and you need to add the changes from last month.
        About the best way to keep two tables in sync... Well, it depends
on
your data volume. If you can afford it, think about "truncate" and then
"insert /*+append*/ ...".
        Also you may take a look of dbms_rectifier_diff.rectify. This is
made for replication, but can do the job.

Rgds,
Yavor

On Fri, 13 Oct 2006 16:30:49 +0300, John J Cantu <
John_J_Cantu@xxxxxxxxxxxx>
wrote:

> All,
>
> The 'Merge' command seems to be incomplete since it doesn't delete
records
> that don't exist.  The 'Merge' command only performs an update or
insert.
> Isn't the purpose of the 'Merge' command to kind of make one table look
> like another one?  Sure there will be times when only updating and
> inserting records is enough to keep the tables insync, but only if
records
> aren't deleted from the source table.  Any comments or suggestions on
the
> best way to keep two tables in sync within the same database?  Will I
have
> to run two commands, one command will be the Merge to update and insert
> and a delete command that queries for keys that don't exist anymore in
the
> source table?
>
> Thanks,
>
> John Cantu



--
//www.freelists.org/webpage/oracle-l


-- //www.freelists.org/webpage/oracle-l



Other related posts: