SQL query to convert duplicate column values to unique value

Need help to come up with an update query to make the value column to be unique values. I use Postgres.

CREATE TABLE test (
  id INT,
  value INT
);
INSERT INTO test (id,value) VALUES ('1','100');
INSERT INTO test (id,value) VALUES ('2','100');
INSERT INTO test (id,value) VALUES ('3','100');
INSERT INTO test (id,value) VALUES ('4','101');
INSERT INTO test (id,value) VALUES ('5','101');
INSERT INTO test (id,value) VALUES ('6','101');

The update should convert the above table to be:

id                      value
==                     =======
1                        100
2                        102
3                        103
4                        101
5                        104
6                        105

Answer

I believe the following update statement will achieve the result you are seeking.

update test 
set value = t.newvalue 
from (
with dupnum as (
  select *, 
  row_number() over (partition by value order by id) rn, 
  max(value) over () maxv 
  from test
)
select 
  id
, row_number() over (order by id) + maxv newvalue
from dupnum where rn > 1) t 
where t.id = test.id