DB2 是否有“插入或更新”功能？陈述？

发布于 2024-07-09 18:13:22 字数 369 浏览 11 评论 0原文

从我的代码（Java）中，我想确保执行代码后数据库（DB2）中存在一行。

我的代码现在执行select，如果没有返回结果，则执行insert。我真的不喜欢这段代码，因为它使我在多线程环境中运行时面临并发问题。

我想要做的是将这个逻辑放入 DB2 而不是我的 Java 代码中。 DB2 有插入或更新语句吗？或者我可以使用类似的东西？

例如：

insertupdate into mytable values ('myid')

另一种方法可能是始终执行插入并捕获“SQL代码-803主键已存在”，但我想如果可能的话避免这种情况。

原文

From my code (Java) I want to ensure that a row exists in the database (DB2) after my code is executed.

My code now does a select and 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.

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-update statement? Or anything like it that I can use?

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧情勿念 2024-07-16 18:13:22

是的，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]

请参阅：

~~http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873 .htm~~

https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0010873。 html

https://www.ibm .com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en

Yes, DB2 has the MERGE statement, which will do an UPSERT (update or insert).

MERGE INTO target_table USING source_table ON match-condition
{WHEN [NOT] MATCHED 
          THEN [UPDATE SET ...|DELETE|INSERT VALUES ....|SIGNAL ...]}
[ELSE IGNORE]

See:

~~http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm~~

https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0010873.html

https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en

回复收藏 0 原文

轮廓§ 2024-07-16 18:13:22

我发现这个线程是因为我确实需要一个用于 DB2 INSERT OR UPDATE 的单行代码。

以下语法似乎可以工作，不需要单独的临时表。

它的工作原理是使用 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)
;

如果您必须插入多行，则可以重复 VALUES 部分，而不必复制其余部分。

VALUES 
    (123, 'text'),
    (456, 'more')

结果是一条语句，可以将插入或更新一行或多行作为原子操作。

I found this thread because I really needed a one-liner for 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.

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 
    (123, 'text'),
    (456, 'more')

The result is a single statement that can INSERT OR UPDATE one or many rows presumably as an atomic operation.

回复收藏 0 原文

后eg是否自 2024-07-16 18:13:22

此响应希望能够完全回答 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;

This response is to hopefully fully answer the query MrSimpleMind had in use-update-and-insert-in-same-query and 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).

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;

回复收藏 0 原文

只等公子 2024-07-16 18:13:22

另一种方法是执行这两个查询。它比创建 MERGE 语句更简单：

update TABLE_NAME set FIELD_NAME=xxxxx where MyID=XXX;

INSERT INTO TABLE_NAME (MyField1,MyField2) values (xxx,xxxxx) 
WHERE NOT EXISTS(select 1 from TABLE_NAME where MyId=xxxx);

如果 MyId 存在，第一个查询仅更新您需要的字段。
如果 MyId 不存在，则第二个将行插入到数据库中。

结果是您的数据库中仅执行其中一个查询。

Another way is to execute this 2 queries. It's simpler than create a MERGE statement:

update TABLE_NAME set FIELD_NAME=xxxxx where MyID=XXX;

INSERT INTO TABLE_NAME (MyField1,MyField2) values (xxx,xxxxx) 
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.

The result is that only one of the queries is executed in your db.

回复收藏 0 原文

心头的小情儿 2024-07-16 18:13:22

我从 hibernate 项目开始，其中 hibernate 允许您 saveOrUpdate()。
我将该项目转换为 JDBC 项目，问题在于保存和更新。
我想使用 JDBC 同时保存和更新。
因此，我做了一些研究，发现了重复密钥更新：

String sql="Insert into tblstudent (firstName,lastName,gender) values (?,?,?) 
ON DUPLICATE KEY UPDATE 
firstName= VALUES(firstName),
lastName= VALUES(lastName),
gender= VALUES(gender)";

上述代码的问题是它更新了主键两次，这是正确的
根据 mysql 文档：
受影响的行只是返回代码。 1 行表示您插入了，2 表示您更新了，0 表示什么也没发生。

我引入了 id 并将其增加到 1。现在我增加的是 id 的值而不是 mysql。

String sql="Insert into tblstudent (id,firstName,lastName,gender) values (?,?,?) 
ON DUPLICATE KEY UPDATE 
id=id+1,
firstName= VALUES(firstName),
lastName= VALUES(lastName),
gender= VALUES(gender)";

上面的代码对我来说适用于插入和更新。

希望它也适合你。

I started with hibernate project where hibernate allows you to saveOrUpdate().
I converted that project into JDBC project the problem was with save and update.
I wanted to save and update at the same time using JDBC.
So, I did some research and I came accross ON DUPLICATE KEY UPDATE :

String sql="Insert into tblstudent (firstName,lastName,gender) values (?,?,?) 
ON DUPLICATE KEY UPDATE 
firstName= VALUES(firstName),
lastName= VALUES(lastName),
gender= VALUES(gender)";

The issue with the above code was that it updated primary key twice which is true as
per mysql documentation:
The affected rows is just a return code. 1 row means you inserted, 2 means you updated, 0 means nothing happend.

I introduced id and increment it to 1. Now I was incrementing the value of id and not mysql.

String sql="Insert into tblstudent (id,firstName,lastName,gender) values (?,?,?) 
ON DUPLICATE KEY UPDATE 
id=id+1,
firstName= VALUES(firstName),
lastName= VALUES(lastName),
gender= VALUES(gender)";

The above code worked for me for both insert and update.

Hope it works for you as well.

回复收藏 0 原文

~没有更多了~