How to perform Update else Insert Operation win INFORMIX without MERGE Statement

Question

Let's Suppose I have two tables - Source and Target. I am trying to Load Target table from Source, and the record should be inserted only if it is not present in the target table, else it should be updated. All the columns should be considered for the comparisons. Is there any options available in Informix other than Merge statement.

Answer 1

As you now know, the MERGE statement was not present in Informix 10.00 (or any version prior to 11.50.xC6).

There isn't a simple way around it.

In outline, the closest approximation is:

1. Identify the primary key columns in the source and target tables — I'm going to assume they're single-column keys with names src_primary_key and tgt_primary_key. Life is more complex if they're multi-column keys, but not insuperably so.

2. Nominally, you would insert the missing records using:

    INSERT INTO Target
        SELECT *
          FROM Source
         WHERE src_primary_key NOT IN (SELECT tgt_primary_key FROM Target)
   

3. However, you probably run foul of restrictions on selecting from the table you're also inserting into, so you end up doing:

    SELECT src_primary_key
      FROM Source
     WHERE src_primary_key NOT IN (SELECT tgt_primary_key FROM Target)
      INTO TEMP Missing_Keys
   
    INSERT INTO Target
        SELECT *
          FROM Source
         WHERE src_primary_key IN (SELECT src_primary_key FROM Missing_Keys)
   

4. Since you want the updates to replace the existing data, you then arrange to create a list of present keys:

    SELECT src_primary_key
      FROM Source
     WHERE src_primary_key IN (SELECT tgt_primary_key FROM Target)
      INTO TEMP Present_Keys;
   
   DELETE FROM Target
    WHERE tgt_primary_key IN (SELECT src_primary_key FROM Present_Keys)
   
   INSERT INTO Target
       SELECT * FROM Source
        WHERE src_primary_key IN (SELECT src_primary_key FROM Present_Keys)
   

5. All of this has to be done inside a transaction to be safe, of course — probably at REPEATABLE READ isolation for maximum safety.

There are probably other ways to do this, but this loosely simulates the steps that the MERGE statement would go through. You might need to deal with 'Present Keys' before dealing with 'Missing Keys' (so execute step 3 after executing step 4). You might also think about whether to simply delete all the rows from Target that match a row in Source, and then simply insert the contents of Source into Target:

BEGIN WORK;
DELETE FROM Target
 WHERE tgt_primary_key IN (SELECT src_primary_key FROM Source);
INSERT INTO Target SELECT * FROM Source;
COMMIT WORK;