Assign new ID to each row dynamically

I have a table with over 9000 rows. Each row has some weird ID. I would like to re-set each ID as following 000001,000002,000003, etc… (dynamically). Is there an easy way of doing it?

Answer

You can update the value to a number:

with toupdate as (
      select t.*, row_number() over (order by (select null)) as new_id
      from t
     )
update toupdate
    set id = new_id;

Or if you want a zero-padded, 6 digit, formatted number:

update toupdate
    set id = format(new_id, '000000');