更新表并返回旧值和新值 [英] Update a table and return both the old and new values

问题描述

我正在编写一个VB应用程序，该应用程序正在擦洗DB2数据库中的某些数据。在一些表中，我想更新整个列。例如，一个帐号栏。我将所有帐号都更改为从1开始，并随着列表的增加而递增。我希望能够同时返回旧帐号和新帐号，因此我可以生成某种我可以参考的报告，这样我就不会丢失原始值。我会这样更新列：

Im writing a VB app that is scrubbing some data inside a DB2 database. In a few tables i want to update entire columns. For example an account number column. I am changing all account numbers to start at 1, and increment as I go down the list. Id like to be able to return both the old account number, and the new one so I can generate some kind of report I can reference so I dont lose the original values. Im updating columns as so:

DECLARE @accntnum INT 
SET @accntnum = 0 
UPDATE accounts
SET @accntnum = accntnum = @accntnum + 1
GO 

是否有

推荐答案

DB2具有真正的漂亮功能，您可以从数据更改语句中选择数据。这已经在用于Linux / Unix / Windows的DB2上进行了测试，但是我认为它至少也应该在 DB2 for z / OS 。

DB2 has a really nifty feature where you can select data from a "data change statement". This was tested on DB2 for Linux/Unix/Windows, but I think that it should also work on at least DB2 for z/OS.

为您的编号，您可以考虑创建序列。那么您的更新将类似于：

For your numbering, you might considering creating a sequence, as well. Then your update would be something like:

 CREATE SEQUENCE acct_seq
     START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO CYCLE
     CACHE 24
;

SELECT accntnum  AS new_acct, old_acct
FROM FINAL TABLE (
    UPDATE accounts INCLUDE(old_acct INT)
    SET accntnum = NEXT VALUE FOR acct_seq, old_acct = accntnum
)
ORDER BY old_acct;

INCLUDE 部分在以下位置创建新列生成的表具有指定的名称和数据类型，然后您可以像其他任何字段一样在update语句中设置值。

The INCLUDE part creates a new column in the resulting table with the name and the data type specified, and then you can set the value in the update statement as you would any other field.

这篇关于更新表并返回旧值和新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！