是否可以为iSeries表中的每一行生成唯一的数值，而不会循环？ [英] Is it possible to generate a unique numeric value for each row in an iSeries table without looping?

问题描述

我有一个具有数千行的iSeries表，并且刚刚添加了一个新的数字/整数列作为表键。这将基本上是一个整数id列（1,2,3 ...）。

我们不能让这个列成为自动递增列。有没有一种简单的方式来为每行分配唯一的增量值，而不必循环遍历每个记录分配值？也许某种UPDATE查询？

解决方案

可以使用Sequence对象。例如

  CREATE SEQUENCE MySeq 
 as {numeric-datatype} 
  

数据类型可以是SMALLINT，INTEGER，BIGINT，DECIMAL或NUMERIC，缩放为零（即无小数位）。创建一个数据区以存储该值。

然后可以使用 NEXT VALUE FOR 表达式来检索和增加序列，如

 更新MyTable SET id = MySeq的下一个值
  

如果您想知道分配的最后一个值，您可以使用表达式 mySeq的上一个值 / p>

此方法不会像RRN（）一样快，但在某些情况下可能会很有用，例如希望在不同表格中具有唯一的数字时。 / p>

I have an iSeries table with thousands of rows, and a new numeric/integer column was just added as the table Key. This will basically be a integer id column (1,2,3...).

Let's say that I can't make this column an auto-incrementing column. Is there an simple way to assign unique, incremental values to each row without having to loop through each record assigning a value? Perhaps some kind of UPDATE query?

解决方案

You could use a Sequence object. For example

CREATE SEQUENCE MySeq
    as {numeric-datatype}

The data type can be SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC, with scale of zero (ie. no decimal places). A data area is created to store the value.

You can then use a NEXT VALUE FOR expression to retrieve and increment the Sequence, as in

UPDATE MyTable SET id = NEXT VALUE FOR MySeq

If you wish to know the last value assigned you can use the expression Previous value for mySeq

This method would not be as fast as RRN(), but may be useful in some cases, such as when you wish to have numbers that are unique across different tables.

这篇关于是否可以为iSeries表中的每一行生成唯一的数值，而不会循环？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！