Hi,

We are on AIX 4.3 with UDB version "DB2 v8.1.1.16".

I would like to know more details about the "upsert" command. What I heard about it is it will insert if the row doesn't exist. If the row exists it will update. I want to try this in our development. Correct me if I'm wrong. Is this neew in V8.x?

This is a joke...right?

Actually, it is no joke. MERGE INTO (aka UPSERT) is a new feature that was introduced at version 8.1.2.

Asram will have to apply either fixpack 2 or 3 to get this new feature.

Andy

It also is a feature of the IMPORT command, but I assume that the IMPORT utility does the checking via code and does not use the MERGE INTO SQL syntax internally.

Prior 8.1 (starting from 7.1 fixpack 3) it was possible to use "upsert", "updel" and so on, for insert/update/delete in one UOW as compound statement.

Check the following article:
http://www-1.ibm.com/support/docview...id=swg21008451

Sample syntax :
BEGIN ATOMIC
FOR row AS
SELECT fact.pkf, stage.pks, stage.c1
FROM fact RIGHT OUTER JOIN stage
ON fact.pkf = stage.pks DO
IF row.pkf IS NULL THEN
INSERT INTO fact VALUES(row.pks, row.c1);
ELSE
UPDATE fact SET c1 = row.c1
WHERE fact.pkf = row.pkf;
END IF;
END FOR;
END