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.

You show one object structure, and then a different update structure, as far as the field names. If you want to get a usable answer, you should make those two match in your question. Otherwise, the logic of your update query is unclear. — vitaly-t, Jul 02 '17 at 23:50

score 1 · Answer 1 · answered Jul 02 '17 at 20:41

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

score 0 · Accepted Answer · edited Dec 18 '18 at 09:07

0

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;";
}

edited Dec 18 '18 at 09:07

Durgpal Singh

11,481
4
37
49

answered Jul 02 '17 at 20:07

Sergey

301
4
6

This was exactly what I needed! I had to make a few tweaks, for example, adding an "else" so that the other items in the table are not affected. Thanks for the help – Cesar Jul 03 '17 at 03:06

vitaly-t · Answer 3 · 2017-07-03T00:13:53.227

If you are using Massive.js 3.0, that means you have access to its underlying driver pg-promise.

Unfortunately, I am not using Massive.js personally, so I don't know enough about doing updates directly through it. I can only advise you as the author of pg-promise, that you can access its driver to do the update you want as shown below.

const db; // your Massive.js database object;

const helpers = db.pgp.helpers; // pg-promise helpers namespace

// reusable ColumnSet:
const cs = new helpers.ColumnSet(['?id', 'amt_purchased'], {table: 'available '});

// your input data:
const data = [{id: 1, amt_purchased: 3}, {id: 2, amt_purchased: 4}];

// query to be executed:
const query = helpers.update(data, cs) + ' WHERE v.id = t.id';

db.instance.none(query)
    .then(() => {
        // success, updated all at once
    })
    .catch(error => {
        // error
    });

Run PostgreSQL UPDATE query for every object in array

3 Answers3