CODEWITHSUNDEEP
sqlarrayspostgresqlsql-updatewhere-clause
kris
kris

Reputation: 129

PostgreSQL: Update multiple rows in table with a single query

Suppose I have a table Car. This table has the following columns: id(uuid), status(text), color(text).

I have pushed all the Car ids that have a specific color into an array:

Suppose I have an array of the Car ids (the size of the array can change):

let car_ids_arr = ['id1', 'id2', ...'idn']

Now I need to update all rows (for example, set status to false) in Car where the id matches the ids in arr. I don't want to do this in a for loop and make several calls to the database, I rather do it all in one call.

The size of my array of ids can change , so I am not sure how to approach this... any pointers?

Upvotes: 3

Views: 3616

Answers (1)

GMB
GMB

Reputation: 222482

That's easily done with array operator any():

update car
set status = false
where id = any(array[1, 2, 3])

If id actually is of uuid datatype:

update car
set status = false
where id = any(array[...]::uuid[])

Upvotes: 4

Related Questions