How to update with incrementing value
postgresql, sql, sql-update
Solution
You may do it step by step with a temporary sequence.
1) creation
create temporary sequence seq_upd;
2) set it to the proper initial value
select setval('seq_upd', (select max(id) from table_name));
3) update
update table_name set id=nextval('seq_upd') where id=1;
Problem
I have a table in PostgreSQL that has an ID column that is supposed to be unique. However, a large number of the rows (around 3 million) currently have an ID of "1". What I know: - The total number of rows - The current maximum value for the ID column - The number of rows with an (incorrect) ID of "1" What I need is a query that will pull all the rows with an ID of "1" and assign them a new ID that increments automatically so that every row in the table will have a unique ID. I'd like it to start at the `currentMaxId + 1` and assign each row the subsequent ID. This is the closest I've gotten with a query: ``` UPDATE table_name SET id = ( SELECT max(id) FROM table_name ) + 1 WHERE id = '1' ``` The problem with this is that the inner SELECT only runs the first time, thus setting the ID of the rows in question to the original `max(id) + 1`, not the new `max(id) + 1` every time, giving me the same problem I'm trying to solve. Any suggestions on how to tweak this query to achieve my desired result or an alternative method would be greatly appreciated!