CODEWITHSUNDEEP
postgresql
Bilbo Bagosz
Bilbo Bagosz

Reputation: 5

postgres - change one column to the same value by name

for example i have column that look like this:

name | id | value 
A      1     aa 
A      2     ab 
B      3     bc 
C      4     ca 
C      5     cb

Is there any way to change it to this ?

name | id | value 
A      1     aa 
A      1     ab 
B      3     bc 
C      4     ca 
C      4     cb

Upvotes: 0

Views: 662

Answers (1)

user330315
user330315

Reputation:

You can do this with a window function that numbers the rows, and use that select statement to supply the values for the update:

update the_table
  set id = t.rn
from (
  select name, 
         id, 
         dense_rank() over (order by name)  as rn
  from the_table
) t
where (t.name, t.id) = (the_table.name, the_table.id);

SQLFiddle example: http://sqlfiddle.com/#!15/0e987/1

This assumes that the existing combination (id, name) is unique. If that is not the case, you would need to use the ctid column to match the rows between the inner select and the table itself:

update the_table
  set id = t.rn
from (
  select name, 
         id, 
         ctid,
         dense_rank() over (order by name)  as rn
  from the_table
) t
where t.ctid = the_table.ctid;

Upvotes: 2

Related Questions