Run PostgreSQL UPDATE query for every object in array

Question

Let's say I have a cart of grocery items and each item has a unique ID. When someone clicks "purchase", an array is sent with an object for each item in that cart. The cart varies so sometimes it might be 2 items and sometimes 6 items, etc.

Example:

[{id: 1, amt_purchased: 3}, {id: 2, amt_purchased: 4}]

I need my SQL table, "grocery items available", to update according to what was purchased.

For one grocery item, I would use the following:

UPDATE available 
SET amt_avail = amt_avail - 3
WHERE produce_id = 1

Since I have multiple items now, how can I get the query to run for each item that was purchased? Or as one massive query that will adapt according to how many items were purchased?

My project is Ionic/AngularJs and NodeJs, Express, MassiveJs.

Thanks guys! Still a noob so I'm having a hard time explaining what I need.

Answer 1

PostgreSQL has an excellent feature UPDATE ... FROM ...; which gives a convenient way to update one table on another table. And second excellent feature - you can use a pseudo-table VALUES in this query:

UPDATE available a
SET amt_available = a.amt_available + v.amt_available
FROM (
  VALUES (1, 25), (3, 15), (7, -55)   -- just pass required values
) AS v (produce_id, amt_available)
WHERE a.produce_id=v.produce_id;

Here's a SQLFiddle to check an idea - http://sqlfiddle.com/#!17/04f24/22