CODEWITHSUNDEEP
teradatarowcount
Kristen
Kristen

Reputation: 183

Updating a column with a row number

I have a large table and need to add row numbers to it in Teradata. I have a column on the table already called row_num but I need to update it with the actual number. I've tried to use the below code but it won't work. 

update database.time
set row_num = INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 1
NO CYCLE))

Any help would be appreciated. Thanks!!

Upvotes: 1

Views: 1390

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you have an identity column that uniquely identifies each row, you can do:

update t
    set t.row_num = tt.seqnum
    from database.time t,
         (select t.*, row_number() over (order by id) as seqnum
          from database.time t
         ) tt
    where t.id = tt.id;

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

update  t

from    mytable t 
      ,(select     t.*
                   ,cast( row_number() over (order by id) as bigint) as rn

        from        mytable t
        ) tt

set     row_num = tt.rn

where   t.id = tt.id
;

Upvotes: 1

Related Questions