xNidhogg xNidhogg - 2 years ago 75
SQL Question

Update the same data on many specific rows

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download