Re: Merge doesn't do delete

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_at_gmail.com> wrote:
>
> In 10g it does do deletes.
> http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Yavor Ivanov
> Sent: Friday, October 13, 2006 9:06 AM
> To: John_J_Cantu_at_raytheon.com
> Cc: oracle-l_at_freelists.org
> 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_at_raytheon.com>
> 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
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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