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
– vitaly-t, Commented Jul 2, 2017 at 23:50

Eugene Lisitsky · Accepted Answer · 2017-07-02 20:41:14Z

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

answered Jul 2, 2017 at 20:41

Eugene Lisitsky

13k6 gold badges42 silver badges63 bronze badges

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

Comments

Durgpal Singh · Accepted Answer · 2018-12-18 09:07:40Z

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, 2018 at 9:07

Durgpal Singh

12k4 gold badges40 silver badges50 bronze badges

answered Jul 2, 2017 at 20:07

Sergey

3094 silver badges6 bronze badges

1 Comment

Cesar Over a year ago

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

vitaly-t · Accepted Answer · 2017-07-03 00:13:53Z

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

Collectives™ on Stack Overflow

Run PostgreSQL UPDATE query for every object in array

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related