Update the same data on many specific rows

Question

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

score 2 · Accepted Answer · answered Feb 28 '13 at 10:49

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.

score 2 · Answer 2 · edited May 23 '17 at 12:23

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.

score 0 · Answer 3 · answered Feb 28 '13 at 10:53

0

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.

answered Feb 28 '13 at 10:53

Jwalin Shah

2,451
1
17
22

Primary keys are indexed by default. – Dariusz Feb 28 '13 at 10:58
if it's primary key then it's ok – Jwalin Shah Feb 28 '13 at 12:24

score 0 · Answer 4 · answered Apr 21 '16 at 18:41

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.

Update the same data on many specific rows

4 Answers4