PostgreSQL: Update multiple rows in table with a single query

Question

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?

GMB · Accepted Answer · 2020-10-30 22:19:14Z

4

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[])

edited Oct 30, 2020 at 22:19

answered Oct 30, 2020 at 21:37

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kris Over a year ago

I am getting an error when issuing this query:

ERROR: operator does not exist: uuid = text   Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

GMB Over a year ago

@kris: you did not mention that we were dealing with uuids. Then follow the error hint and do cast. See my updated answer.

kris Over a year ago

Also, what if I want to use my array variable car_ids_arr in the query instead of array[...]?

Collectives™ on Stack Overflow

PostgreSQL: Update multiple rows in table with a single query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related