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.
update available
set amt_avail = case
when produce_id = 1 then amt_avail - 3
when produce_id = 2 then amt_avail - 4
end;
You could use simple for loop to build such query
var items = [{id: 1, amt_purchased: 3}, {id: 2, amt_purchased: 4}];
function buildQuery(items) {
var query = "update available set amt_avail = case ";
for (var i = 0; i < items.length; i++) {
var item = items[i];
query += `when produce_id = ${item['id']} then amt_avail - ${item['amt_purchased']}`
}
return query += " end;";
}