Insert into a Informix table or update if exists

Question

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

Answer 1

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).