Use SQL To Update A Sequence Number
November 14, 2012 Hey, Ted
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.
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 the sequence generator.
create sequence renumber start with 10 increment by 10 no maxvalue no cycle
Update your table.
update testseq set SerialNbr = next value for renumber
Take another look at the data.
If you’re finished with the sequence generator, you can get rid of it.
drop sequence renumber
If not, you can leave it around for next time. The sequence will start where it left off.
Maybe that’s an answer to your question.
You can also use a sequence generator when adding data to a table.
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')
This looks like a good way to generate unique key values. Maybe I’ll get a chance to use it for real someday.