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

[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

[Chris]

Sweet!! Thanks Chris

[Eugene F]

Serge,

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

-Eugene

[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/

[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

[Chris]

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

Chris