CODEWITHSUNDEEP
sqldatabasepostgresqlpostgresql-9.1
xNidhogg
xNidhogg

Reputation: 341

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

Upvotes: 0

Views: 175

Answers (4)

pyrospade
pyrospade

Reputation: 8078

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.

Upvotes: 0

user1919238
user1919238

Reputation:

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.

Upvotes: 2

Jwalin Shah
Jwalin Shah

Reputation: 2521

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.

Upvotes: 0

Dariusz
Dariusz

Reputation: 22241

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.

Upvotes: 2

Related Questions