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

问题描述

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

我正在为 IBM iSeries 机器上的 DB2 编写查询.我见过 CASE 关键字，但我不能让它工作.我总是收到错误:没有预期的关键字 CASE."

示例:

<上一页>如果存在(SELECT * FROM MYTABLE WHERE KEY = xxx)然后更新 MYTABLE SET VALUE = zzz WHERE KEY = xxxELSE INTO MYTABLE (KEY, VALUE) 值 (xxx, zzz)万一

有没有办法针对 IBM iSeries 上的 DB2 执行此操作?目前，我运行两个查询.首先是一个选择，然后我的 Java 代码决定更新/插入.我宁愿写一个查询，因为我的服务器位于很远的地方(横跨太平洋).

解决方案

AS/400 上的 DB/2 没有条件 INSERT/UPDATE 语句.

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

更快的选择是在 QTEMP 中创建一个临时表，INSERT 将所有记录放入临时表中，然后执行批量 UPDATE ... WHERE EXISTS 和 INSERT ... WHERE NOT EXISTS 将所有记录合并到最终表中.这种方法的优点是您可以将所有语句包装在一个批处理中，以最大程度地减少往返通信.

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

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

Sample:

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

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 on the AS/400 does not have a conditional INSERT / UPDATE statement.

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.

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屋！