SQL DB2 是否有“插入或更新”语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/330241/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does DB2 have an "insert or update" statement?
提问by Mikael Eriksson
From my code (Java) I want to ensure that a row exists in the database (DB2) after my code is executed.
从我的代码 (Java) 中，我想确保在执行我的代码后数据库 (DB2) 中存在一行。
My code now does a selectand if no result is returned it does an insert. I really don't like this code since it exposes me to concurrency issues when running in a multi-threaded environment.
我的代码现在执行 a select，如果没有返回结果，则执行insert. 我真的不喜欢这段代码，因为它让我在多线程环境中运行时遇到并发问题。
What I would like to do is to put this logic in DB2 instead of in my Java code.
Does DB2 have an insert-or-updatestatement? Or anything like it that I can use?
我想要做的是将此逻辑放在 DB2 中而不是我的 Java 代码中。DB2 有insert-or-update声明吗？或者我可以使用的任何类似的东西？
For example:
例如：
insertupdate into mytable values ('myid')
Another way of doing it would probably be to always do the insert and catch "SQL-code -803 primary key already exists", but I would like to avoid that if possible.
另一种方法可能是始终执行插入并捕获“SQL 代码 -803 主键已经存在”，但如果可能，我想避免这种情况。
回答by Winston Smith
Yes, DB2 has the MERGE statement, which will do an UPSERT (update or insert).
是的，DB2 有 MERGE 语句，它将执行 UPSERT（更新或插入）。
MERGE INTO target_table USING source_table ON match-condition
{WHEN [NOT] MATCHED
THEN [UPDATE SET ...|DELETE|INSERT VALUES ....|SIGNAL ...]}
[ELSE IGNORE]
See:
看：
https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en
https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en
回答by teknopaul
I found this thread because I really needed a one-liner for DB2 INSERT OR UPDATE.
我找到了这个线程，因为我真的需要一个单行的 DB2 INSERT OR UPDATE。
The following syntax seems to work, without requiring a separate temp table.
以下语法似乎有效，不需要单独的临时表。
It works by using VALUES() to create a table structure . The SELECT * seems surplus IMHO but without it I get syntax errors.
它通过使用 VALUES() 创建表结构来工作。SELECT * 似乎多余恕我直言，但没有它我会收到语法错误。
MERGE INTO mytable AS mt USING (
SELECT * FROM TABLE (
VALUES
(123, 'text')
)
) AS vt(id, val) ON (mt.id = vt.id)
WHEN MATCHED THEN
UPDATE SET val = vt.val
WHEN NOT MATCHED THEN
INSERT (id, val) VALUES (vt.id, vt.val)
;
if you have to insert more than one row, the VALUES part can be repeated without having to duplicate the rest.
如果您必须插入多于一行，则可以重复 VALUES 部分而不必重复其余部分。
VALUES
(123, 'text'),
(456, 'more')
The result is a single statement that can INSERT OR UPDATE one or many rows presumably as an atomic operation.
结果是一个可以插入或更新一行或多行的单个语句，大概是一个原子操作。
回答by CupOfTea
This response is to hopefully fully answer the query MrSimpleMind had in use-update-and-insert-in-same-queryand to provide a working simple example of the DB2 MERGE statement with a scenario of inserting AND updating in one go (record with ID 2 is updated and record ID 3 inserted).
这个响应是希望完全回答 MrSimpleMind 在use-update-and-insert-in-same-query 中的查询，并提供一个简单的 DB2 MERGE 语句示例，其中包含一次性插入和更新的场景（记录更新 ID 2 并插入记录 ID 3）。
CREATE TABLE STAGE.TEST_TAB ( ID INTEGER, DATE DATE, STATUS VARCHAR(10) );
COMMIT;
INSERT INTO TEST_TAB VALUES (1, '2013-04-14', NULL), (2, '2013-04-15', NULL); COMMIT;
MERGE INTO TEST_TAB T USING (
SELECT
3 NEW_ID,
CURRENT_DATE NEW_DATE,
'NEW' NEW_STATUS
FROM
SYSIBM.DUAL
UNION ALL
SELECT
2 NEW_ID,
NULL NEW_DATE,
'OLD' NEW_STATUS
FROM
SYSIBM.DUAL
) AS S
ON
S.NEW_ID = T.ID
WHEN MATCHED THEN
UPDATE SET
(T.STATUS) = (S.NEW_STATUS)
WHEN NOT MATCHED THEN
INSERT
(T.ID, T.DATE, T.STATUS) VALUES (S.NEW_ID, S.NEW_DATE, S.NEW_STATUS);
COMMIT;
回答by Felipe
Another way is to execute this 2 queries. It's simpler than create a MERGE statement:
另一种方法是执行这两个查询。它比创建 MERGE 语句更简单：
update TABLE_NAME set FIELD_NAME=xxxxx where MyID=XXX;
INSERT INTO TABLE_NAME values (MyField1,MyField2)
WHERE NOT EXISTS(select 1 from TABLE_NAME where MyId=xxxx);
The first query just updateS the field you need, if the MyId exists. The second insertS the row into db if MyId does not exist.
如果 MyId 存在，第一个查询只会更新您需要的字段。如果 MyId 不存在，则第二个将行插入 db。
The result is that only one of the queries is executed in your db.
结果是在您的数据库中只执行了其中一个查询。
