How to update table with sequentional on table without primary key?

Question

In DB2 on Linux v11.1 I have a table:

COL1   COL2  "COLn 50 more columns"
A      A
A      A
B      A
B      B
etc 3 million rows

There can be multiple rows with the same rows, like first two rows in my sample (so obvious there is no primary key on table).

Now I have to add new column ID and set for every row unique sequential number. The result should be:

COL1   COL2  "COLn 50 more columns"   ID
A      A                               1
A      A                               2
B      A                               3
B      B                               4
etc 3 million rows

How to write such an update statement to update ID column? Regards

Answer 1

Here is one way to do it, using an identity column , and it assumes that there is not an existing Primary Key or identity column.

alter table myschema.mytab add column id integer not null default 0 ;

alter table myschema.mytab alter column id drop default ;


alter table myschema.mytab alter column id set generated always as identity ;


update myschema.mytab set id = default ;

-- optional, if you want the new ID column to be a surrogate primary key

alter table myschema.mytab add constraint pkey primary key(id) ;

reorg table myschema.mytab ;

runstats on table myschema.mytab with distribution and detailed indexes all;