如何只更新 DB2 中的一条记录? [英] How to UPDATE just one record in DB2?

问题描述

在 DB2 中，我需要做一个 SELECT FROM UPDATE，将更新 + 选择放在一个事务中.
但我需要确保每笔交易只更新一条记录.

In DB2, I need to do a SELECT FROM UPDATE, to put an update + select in a single transaction.
But I need to make sure to update only one record per transaction.

熟悉 MySQL 的 LIMIT 子句更新选项

Familiar with the LIMIT clause from MySQL's UPDATE option

限制可更新的行数

我在 DB2 的 UPDATE 参考 但没有成功.

I looked for something similar in DB2's UPDATE reference but without success.

如何在 DB2 中实现类似的功能?

How can something similar be achieved in DB2?

在我的场景中，我必须根据要求提供 1000 个优惠券代码.我只需要选择(任何)尚未给出的.

In my scenario, I have to deliver 1000 coupon codes upon request. I just need to select (any)one that has not been given yet.

推荐答案

这个问题使用了一些模棱两可的术语，这使得它不清楚需要完成什么.幸运的是，DB2 为各种 SQL 模式提供了强大的支持.

The question uses some ambiguous terminology that makes it unclear what needs to be accomplished. Fortunately, DB2 offers robust support for a variety of SQL patterns.

限制 UPDATE 修改的行数:

UPDATE 
( SELECT t.column1 FROM someschema.sometable t WHERE ... FETCH FIRST ROW ONLY
) 
SET column1 = 'newvalue';

UPDATE 语句永远不会看到基表，只会看到过滤它的表达式，因此您可以控制更新哪些行.

---

INSERT 有限数量的新行:

The UPDATE statement never sees the base table, just the expression that filters it, so you can control which rows are updated.

---

To INSERT a limited number of new rows:

INSERT INTO mktg.offeredcoupons( cust_id, coupon_id, offered_on, expires_on )
SELECT c.cust_id, 1234, CURRENT TIMESTAMP, CURRENT TIMESTAMP + 30 DAYS 
FROM mktg.customers c 
LEFT OUTER JOIN mktg.offered_coupons o
ON o.cust_id = c.cust_id
WHERE ....
AND o.cust_id IS NULL
FETCH FIRST 1000 ROWS ONLY;

---

这就是 DB2 如何支持 UPDATE、INSERT 或 DELETE 语句中的 SELECT:

---

This is how DB2 supports SELECT from an UPDATE, INSERT, or DELETE statement:

SELECT column1 FROM NEW TABLE (
    UPDATE ( SELECT column1 FROM someschema.sometable 
             WHERE ... FETCH FIRST ROW ONLY
    ) 
    SET column1 = 'newvalue'
) AS x;

SELECT 将仅从修改的行返回数据.

The SELECT will return data from only the modified rows.

这篇关于如何只更新 DB2 中的一条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！