MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » February 2014

Re: SQL Merge vs. CPYF MBROPT(*UPDADD)



fixed

Scott,

Sounds like you wanted an *ADDNEW vs. *UPDADD....so sure merger might make
sense. I'll readily agree that MERGE is much more powerful than CPYF
*UPDADD.

But the original question assuming CPYF *UPDADD fits your needs, is there
any reason to use MERGE?

Charles


On Mon, Feb 3, 2014 at 5:14 PM, Scott Mildenberger <
SMildenberger@xxxxxxxxxxxxxxxxxx> wrote:

How about the capability to do an IGNORE? I have a web service that I
retrieve records from, some of the records are duplicates due to how a GPS
generates records. I use a MERGE statement to INSERT if the record doesn't
already exist and to IGNORE it if it is already in the database. Obviously
there are other ways to handle this but the MERGE seemed cleaner to me.

Scott

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, February 03, 2014 2:14 PM
To: Midrange Systems Technical Discussion
Subject: SQL Merge vs. CPYF MBROPT(*UPDADD)

I'm glad that DB2 for i finally got the SQL MERGER statement...

And I see that it offers lot's of functionality...

However, for a basic merge ie.
MERGE INTO archive ar
USING (SELECT activity, description FROM activities) ac ON (ar.activity =
ac.activity) WHEN MATCHED THEN UPDATE SET description = ac.description WHEN
NOT MATCHED THEN INSERT (activity, description) VALUES(ac.activity,
ac.description)

I don't really see any benefits over CPYF MBROPT(*UPDADD) FMT(*MAPDROP).
Other than perhaps the ability to use commitment control with merge.

Am I missing anything?

Charles
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact