DB2 for IBM iSeries：IF EXISTS语句语法 [英] DB2 for IBM iSeries: IF EXISTS statement syntax

问题描述

我熟悉Sybase，它允许使用以下格式的查询：IF EXISTS（）THEN ... ELSE ... END IF（或非常接近）。这是一个强大的语句，允许：如果存在，则更新，否则插入。

I am familiar with Sybase which allows queries with format: IF EXISTS () THEN ... ELSE ... END IF (or very close). This a powerful statement that allows: "if exists, then update, else insert".

我在IBM iSeries框上编写DB2查询。我见过CASE关键字，但我无法使其工作。我总是收到错误：关键字CASE不是预期的。

I am writing queries for DB2 on IBM iSeries box. I have seen the CASE keyword, but I cannot make it work. I always receive the error: "Keyword CASE not expected."

示例：

IF EXISTS ( SELECT * FROM MYTABLE WHERE KEY = xxx )
THEN UPDATE MYTABLE SET VALUE = zzz WHERE KEY = xxx
ELSE INSERT INTO MYTABLE (KEY, VALUE) VALUES (xxx, zzz)
END IF

有没有办法在IBM iSeries上针对DB2做这件事？目前，我运行两个查询。首先是select，然后我的Java代码决定更新/插入。我宁愿写一个查询，因为我的服务器位于远处（横跨太平洋）。

Is there a way to do this against DB2 on IBM iSeries? Currently, I run two queries. First a select, then my Java code decides to update/insert. I would rather write a single query as my server is located far away (across the Pacific).

推荐答案

DB / 2 AS / 400没有条件 INSERT / UPDATE 语句。

DB/2 on the AS/400 does not have a conditional INSERT / UPDATE statement.

您可以通过直接执行 INSERT 来删除 SELECT 语句，如果失败则执行更新声明。如果您的数据更可能 UPDATE 而不是 INSERT ，则翻转语句的顺序。

You could drop the SELECT statement by executing an INSERT directly and if it fails execute the UPDATE statement. Flip the order of the statements if your data is more likely to UPDATE than INSERT.

更快的选择是在 QTEMP 中创建临时表， INSERT 所有将记录放入临时表，然后执行批量 UPDATE ... WHERE EXISTS 和 INSERT ... WHERE NOT EXISTS 最后将所有记录合并到最终表中。此方法的优点是您可以批量包装所有语句以最小化往返通信。

A faster option would be to create a temporary table in QTEMP, INSERT all of the records into the temporary table and then execute a bulk UPDATE ... WHERE EXISTS and INSERT ... WHERE NOT EXISTS at the end to merge all of the records into the final table. The advantage of this method is that you can wrap all of the statements in a batch to minimize round trip communication.

这篇关于DB2 for IBM iSeries：IF EXISTS语句语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！