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
You probably should use the MERGE statement.
Given a suitable
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).