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

问题描述

在 DB2 中，我需要执行 SELECT FROM UPDATE ，以便在单个事务中添加更新+ select。

需要确保每次交易只更新一个记录。

熟悉 LIMIT 子句 MySQL的 UPDATE 选项

对可更新的行数设置限制

< blockquote>

我在 DB2 UPDATE 参考，但没有成功。 / p>

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

---

em>编辑： 在我的情况下，我要交付1000优惠券代码。

解决方案

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

要限制由 UPDATE ：

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

UPDATE 语句从不会看到基表，只是对其进行过滤的表达式，因此您可以控制要更新的行。

---

到 INSERT 有限数量的新行：

  INSERT INTO mktg.offeredcoupons（cust_id，coupon_id，offered_on，expires_on）
 SELECT c.cust_id，1234，CURRENT TIMESTAMP，CURRENT TIMESTAMP + 30 DAY 
 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只要; 
 

---

这是DB2如何支持 SELECT ，， UPDATE ， INSERT $ c>语句：

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

SELECT 已修改的行。

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.

Familiar with the LIMIT clause from MySQL's UPDATE option

places a limit on the number of rows that can be updated

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

How can something similar be achieved in DB2?

---

Edit: 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.

解决方案

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.

To limit the number of rows that are modified by an UPDATE:

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

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;

---

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;

The SELECT will return data from only the modified rows.

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