467,185 Members | 1,276 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,185 developers. It's quick & easy.

Merge Alternative

Ok, this should be simple, but I banging my head on this one:

Is there an Update statment that can accomplish the same as this Merged
statement?

MERGE INTO New_Table NT
USING Old_Table OT
ON OT.id = NT.id
WHEN MATCHED THEN
UPDATE
SET NT.Foo = OT.Foo

I'm on iSeries DB2, hence MERGE is not supported??

Tx

Chris

Mar 20 '06 #1
  • viewed: 2745
Share:
6 Replies
Chris wrote:
Ok, this should be simple, but I banging my head on this one:

Is there an Update statment that can accomplish the same as this Merged
statement?

MERGE INTO New_Table NT
USING Old_Table OT
ON OT.id = NT.id
WHEN MATCHED THEN
UPDATE
SET NT.Foo = OT.Foo

I'm on iSeries DB2, hence MERGE is not supported??

UPDATE New_Table NT
SET NT.Foo = (SELECT OT.Foo FROM Old_Table OT WHERE OT.id = NT.id)
WHERE NOT EXISTS(SELECT 1 FROM Old_Table OT WHERE OT.id = NT.id)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 20 '06 #2
Sweet!! Thanks Chris

Mar 20 '06 #3
Serge,

Why *NOT* EXISTS? Looks like the update needs matching row ids, isn't
it?

-Eugene

Mar 21 '06 #4
Eugene F wrote:
Why *NOT* EXISTS? Looks like the update needs matching row ids, isn't
it?


Because otherwise the unmatched rows get set to null, which is not,
typically, what you had in mind.

--
Jonathan Leffler #include <disclaimer.h>
Email: jl******@earthlink.net, jl******@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Mar 21 '06 #5
Eugene F wrote:
Serge,

Why *NOT* EXISTS? Looks like the update needs matching row ids, isn't
it?

Oops.. call it a test to see whether everyone is awake ;-)
WHERE EXISTS(....) is correct of course.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 21 '06 #6
Yeah, I noticed that too but I figured out what needed to be changed.
Tx again.

Chris

Mar 21 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Aaron | last post: by
8 posts views Thread by Squirrel | last post: by
4 posts views Thread by John J. Hughes II | last post: by
2 posts views Thread by cashdeskmac | last post: by
7 posts views Thread by giladp1@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.