如果存在则更新，否则插入到Informix表中。

Question

我想向一个Informix数据库表中添加一行，但是当已存在具有相同唯一键的行时，我想更新该行。

我在这里找到了MySQL的解决方案（链接），如下所示，但我需要适用于Informix：

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

- user2241537

Answer 1

你可能应该使用MERGE语句。

给定一个适当的表：

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

这段SQL是有效的：

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有有趣的规则，允许使用关键字作为标识符而无需双引号（实际上，除非在环境中设置了DELIMIDENT，否则双引号只是字符串周围单引号的替代品）。

- Jonathan Leffler

---

非常棒的解决方案；但是，如果我想要插入/更新的值之一为null，则在使用“null AS columnName”时，Informix 11.7会生成系统错误。您可以通过使用“nvl（null，null）as columnName”来解决此问题。此外，我还必须使用FROM sysmaster：informix.sysdual（informix周围没有引号）。 - Martyn Davis

我现在无法进行测试，但请尝试使用NULL :: INTEGER（使用列的类型）。如果这不起作用，我将不得不进行实验。 - Jonathan Leffler

如果源表中的行违反了目标表中的唯一索引（例如具有重复的ID），Informix数据库仍然会抱怨并抛出错误。这与“INSERT ... ON DUPLICATE KEY UPDATE ...”大不相同。 - Rockallite

Answer 2

您可以使用 MERGE 语句尝试相同的行为：

例如，创建目标表：

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

创建一个临时源表并插入所需记录：

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

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

合并操作将会是：

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

您会看到记录已经插入，然后您可以：

UPDATE  source
SET     age = 20
WHERE   id = 1;

并再次测试合并操作。

另一种方法是创建一个存储过程，基本上您需要执行INSERT语句并检查SQL错误代码，如果是-100，则使用UPDATE进行处理。

类似于：

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;

- Ricardo Henriques

---

MERGE与INSERT ... ON DUPLICATE KEY UPDATE ...相去甚远。在上面的例子中，只需在源表中添加另一行具有重复ID的记录，例如：INSERT INTO source (id, name, age) VALUES (1, 'A', 19); INSERT INTO source (id, name, age) VALUES (1, 'B', 20);，然后再试一次。Informix数据库会报错Could not insert new row - duplicate value in a UNIQUE INDEX column。 - Rockallite

是的，@Rockallite，有一些规则和限制。现在，你有实际的问题吗？看起来你正在追求另一个测试用例，在这种情况下是迁移。你能更具体地说明目标、使用的版本等吗？但这应该是另一个问题。 - Ricardo Henriques