Danish Amjad Danish Amjad - 1 year ago 272
SQL Question

Insert into a Informix table or update if exists

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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download