db2等价于MySql REPLACE INTO [英] db2 equivalent of MySql REPLACE INTO

问题描述

我非常有用的声明我喜欢MySql是 REPLACE INTO表，即'REPLACE a value if-exists OR INSERT INTO table If-Not-Exist'。

db2 REPLACE 的文档适用于在字符串上运行的函数，不具有这种含义。 >

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

解决方案

DB2使用SQL标准 MERGE 声明做基本相同的事情。语法不同：

  MERGE INTO table_to_upsert AS tab 
 USING（VALUES 
（1，2， 3）
（4，5，6），
（7,8,9）
  - 更多行
）AS合并（C1，C2，C3）
 ON tab.key_to_match = merge.key_to_match 
当匹配
更新SET tab.C1 = merge.C1，
 tab.C2 = merge.C2，
 tab.C3 = merge.C3 
不符合
 INSERT（C1，C2，C3）
 VALUES（merge.C1，merge.C2，merge.C3）
  

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

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

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

解决方案

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)

这篇关于db2等价于MySql REPLACE INTO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！