Use SQL To Update A Sequence Number

Is it possible to use a single SQL statement to assign an ascending sequence number to a column in a table? I’d like the sequence number to start at 10 and increment by 10 as every row is updated so that the number column in the updated rows would be 10, 20, 30, etc.

–Doug

I know a way, Doug. However, let me say up front that I’ve only played with this. That is, I’ve never used it in a production environment. I can’t speak to how practical it might be or what you might need to watch out for.

DB2 for i provides a way to create an object that generates a sequence of numbers. You can use this object to update your database table (physical file). Here’s an example.

First, create a table to play with.

create table TestSeq
(SerialNbr dec(5,0), Name char(24))

Next, put some data into the table.

insert into testseq (Name) values 
('Bob White-Quayle'),
('Billy Doo'),
('Jack O''Napes')

Here’s what the data looks like.

create sequence renumber
start with 10
increment by 10
no maxvalue
no cycle

update testseq
   set SerialNbr = next value for renumber

drop sequence renumber

insert into testseq (SerialNbr, Name) values
(next value for renumber, 'Bob White-Quayle'),
(next value for renumber, 'Billy Doo'),
(next value for renumber, 'Jack O''Napes')