db2 相当于 MySql REPLACE INTO

2022-01-14 00:00:00 sql db2 mysql

我喜欢 MySql 的非常有用的语句是 REPLACE INTO 表，即替换值 if-exist OR INSERT INTO table If-Not-Exist".

I very useful statement I like of MySql is REPLACE INTO table that is 'REPLACE a value if-exist OR INSERT INTO table If-Not-Exist'.

db2 REPLACE 的文档是针对仅对字符串进行操作的函数，因此没有这个含义.

The documentation of db2 REPLACE is for a function operating on strings only so not with that meaning.

在 db2 中是否有任何等价物?现在我也在寻找关键字IF EXSTS/IF NOT EXIST.

Is there any equivalent in db2? Right now I am looking also for keywords IF EXSTS/IF NOT EXIST.

推荐答案

DB2 使用 SQL 标准 MERGE 语句做基本相同的事情.语法不同:

DB2 uses the SQL standard MERGE statement to do basically the same thing. The syntax is different:

MERGE INTO table_to_upsert AS tab
USING (VALUES
        (1, 2, 3),
        (4, 5, 6),
        (7, 8, 9)
        -- more rows
    ) AS merge (C1, C2, C3)
    ON tab.key_to_match = merge.key_to_match
    WHEN MATCHED THEN
        UPDATE SET tab.C1 = merge.C1,
                   tab.C2 = merge.C2,
                   tab.C3 = merge.C3
    WHEN NOT MATCHED THEN
        INSERT (C1, C2, C3)
        VALUES (merge.C1, merge.C2, merge.C3)

相关文章