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
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;
(id, values) and i want to do the same, i need to use ctid, right ?(1275868, 1275958, 1275910) how i can resolve the same problem? Is there any function like to dense_rank which can handle with that ?A | 152 | ca A | 173 | cb B | 163 | ba C | 285 | bb C | 273 | be And i want to look like this: A | 152 | ca A | 152 | cb B | 163 | ba C | 285 | bb C | 285 | be
updatestatement) or do you just want to display the values like that (i.e. in aselectstatement)