CODEWITHSUNDEEP
sqlinformix
user2241537
user2241537

Reputation:

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

Upvotes: 5

Views: 9209

Answers (2)

Ricardo Henriques
Ricardo Henriques

Reputation: 1086

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;

Upvotes: 4

Jonathan Leffler
Jonathan Leffler

Reputation: 754880

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

Upvotes: 11

Related Questions