5

I want to add a row to an Informix database table, but when a row exists with the same unique key I want to update the row.

I have found a solution for MySQL here which is as follows but I need it for Informix:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name="A", age=19
Community
  • 1
  • 1

2 Answers2

10

You probably should use the MERGE statement.

Given a suitable table:

create table table (id serial not null primary key, name varchar(20) not null, age integer not null);

this SQL works:

MERGE INTO table AS dst
    USING (SELECT 1 AS id, 'A' AS name, 19 AS age
             FROM sysmaster:'informix'.sysdual
          ) AS src
    ON dst.id = src.id
    WHEN NOT MATCHED THEN INSERT (dst.id, dst.name, dst.age)
         VALUES (src.id, src.name, src.age)
    WHEN MATCHED THEN UPDATE SET dst.name = src.name, dst.age = src.age

Informix has interesting rules allowing the use of keywords as identifiers without needing double quotes (indeed, unless you have DELIMIDENT set in the environment, double quotes are simply an alternative to single quotes around strings).

Jonathan Leffler
  • 666,971
  • 126
  • 813
  • 1,185
  • Fantastic solution; however, if one of the values I want to insert/update is null, Informix 11.7 generates a system error when "null AS columnName" is used. You can get around this by using "nvl(null, null) as columnName". Also I had to use FROM sysmaster:informix.sysdual (no quotes around informix) – Martyn Davis Feb 19 '18 at 21:39
  • I can’t test at the moment but try using NULL::INTEGER (use the type of the column). If that doesn’t work, I’ll have to experiment. – Jonathan Leffler Feb 19 '18 at 21:42
  • If the source table have rows that violate a unique index in the target table (e.g. having duplicated ids), the Informix database will still complain and throw an error. Far different from `INSERT ... ON DUPLICATE KEY UPDATE ...`. – Rockallite Jul 24 '19 at 02:39
4

You can try the same behavior using the MERGE statement:

Example, creation of the target table:

CREATE TABLE target 
(
    id      SERIAL PRIMARY KEY CONSTRAINT pk_tst,
    name    CHAR(1),
    age     SMALLINT
);

Create a temporary source table and insert the record you want:

CREATE TEMP TABLE source
(
    id      INT,
    name    CHAR(1),
    age     SMALLINT
) WITH NO LOG;

INSERT INTO source (id, name, age) VALUES (1, 'A', 19);

The MERGE would be:

MERGE INTO target AS t 
USING source AS s ON t.id = s.id

WHEN MATCHED THEN 
    UPDATE
    SET t.name = s.name, t.age = s.age

WHEN NOT MATCHED THEN 
    INSERT (id, name, age) 
    VALUES (s.id, s.name, s.age);

You'll see that the record was inserted then you can:

UPDATE  source
SET     age = 20
WHERE   id = 1;

And test the MERGE again.

Another way to do it is create a stored procedure, basically you will do the INSERT statement and check the SQL error code, if it's -100 you go for the UPDATE.

Something like:

CREATE PROCEDURE sp_insrt_target(v_id INT, v_name CHAR(1), v_age SMALLINT)
    ON EXCEPTION IN (-100)
        UPDATE target
        SET name = v_name, age = v_age
        WHERE id = v_id;
    END EXCEPTION
    INSERT INTO target VALUES (v_id, v_name, v_age);
END PROCEDURE;
marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Ricardo Henriques
  • 1,026
  • 6
  • 13
  • `MERGE` isn't even close to `INSERT ... ON DUPLICATED KEY UPDATE ...`. In the above example, just add another row in the source table with a duplicated id, e.g. `INSERT INTO source (id, name, age) VALUES (1, 'A', 19); INSERT INTO source (id, name, age) VALUES (1, 'B', 20);` and try again. The Informix database would complain `Could not insert new row - duplicate value in a UNIQUE INDEX column`. – Rockallite Jul 24 '19 at 02:36
  • Yes @Rockallite there are some rules and limitations. Now, is there an actual question in there? Seems like you are chasing another test case, a migration in that case. Can you be more specific with the goals, versions in use,...But that should be another question. – Ricardo Henriques Jul 25 '19 at 07:23