2

I have a table on PostgreSQL and 1000 rows in it. I have an integer column in my table that is empty. I want to populate newid with sequential, unique numbers a bit like a primary key.

Product
-------------------------
id  name  newid  size
60   ....  null   L
72   ....  null   M
83   ....  null   xl
84   ....  null   S
85   ...

How can I write a query that update my newid column.

Product
-------------------------
id  name  newid  size
60   ....  1      L
72   ....  2      M
83   ....  3      xl
84   ....  4      S
85   ...
shA.t
  • 16,580
  • 5
  • 54
  • 111
barteloma
  • 6,403
  • 14
  • 79
  • 173

2 Answers2

6

You can do this using JOIN or subquery. The idea is to calculate the new id using row_number() and then bring that value into each row:

with newvals (
      select p.*, row_number() over (order by id) as seqnum
      from product p
     )
update product p
    set newid = (select seqnum from newvals nv where nv.id = p.id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

How about:

update mytable set
newid = id + 1000000
Bohemian
  • 412,405
  • 93
  • 575
  • 722