Merge Alternative

Chris

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

Serge Rielau

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

Chris

Sweet!! Thanks Chris

Mar 20 '06 #3

Eugene F

Serge,

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

-Eugene

Mar 21 '06 #4

Jonathan Leffler

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

Serge Rielau

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

Chris

Yeah, I noticed that too but I figured out what needed to be changed.
Tx again.

Chris

Mar 21 '06 #7