I want to update multiple rows. I have a lot of ids that specify which row to update (around 12k ids).
What would be the best way to achieve this?
I know I could do
UPDATE table SET col="value" WHERE id = 1 OR id = 24 OR id = 27 OR id = ....repeatx10000 But I figure that would give bad performance, right? So is there a better way to specify which ids to update?
Postgresql version is 9.1
In terms of strict update performance not much will change. All rows with given IDs must be found and updated.
One thing that may simplify your call is to use the in keyword. It goes like this:
UPDATE table SET col="value" WHERE id in ( 1,24,27, ... );
I would also suggest making sure that the ID's are in the same order like the index on the id suggests, probably ascending.
Put your IDs in a table. Then do something like this:
UPDATE table SET col="value" WHERE id in (select id from table_of_ids_to_update) Or if the source of your ids is some other query, use that query to get the ids you want to update.
UPDATE table SET col="value" WHERE id in ( select distinct id from some_other_table where some_condition_for_updating is true ... etc. ... ) For more complex cases of updating based on another table, this question gives a good example.
UPDATE table SET col="value" WHERE id in ( select id from table);
Also make indexing on your id field so, you will get better performance.
It's worth noting that if you do reference a table as @dan1111 suggests, don't use in (select ..), and certainly avoid distinct! Instead, use exists -
update table set col = value where exists ( select from other_table where other_table.id = table.id ) This ensures that the reference table is only scanned as much as it is needed.
