Adding multiple values to multiple rows in nodejs mysql

Question

I have a table that holds products and a column for total amount sold (amount_sold), after an order has gone through I want to add the value bought to this column.
I have an array that will look like:

[{sku: "SKU1", purchased: 1},
{sku: "SKU2", purchased: 2}],

For multiple updates normally there's

UPDATE product SET (?) WHERE sku_id in (?)

but I want to add the purchased to the amount sold column somthing like:

UPDATE product  SET amount_sold = amount_sold + 1 WHERE sku_id in (?)

Obviously a customer might not purchase 1 of everything they buy. Would I have to do a loop through a array to add these values? Or is there a way to do it in one query? Thanks

Interesting the SQL you say works looks wrong and the one you say does not work looks right. No idea what is going on. — Hogan
– Hogan, Commented Feb 12, 2021 at 21:19

Barmar · Accepted Answer · 2021-02-12 21:29:59Z

1

The query should look like:

UPDATE product
SET amount_sold = amount_sold + 
    CASE sku_id
        WHEN ? THEN ?
        WHEN ? THEN ?
        ...
    END
WHERE sku_id IN (?)

Since the number of WHEN clauses varies, you'll need to build this dynamically.

let purchases = [{
  sku: "SKU1",
  purchased: 1
}, {
  sku: "SKU2",
  purchased: 2
}];
let whens = "WHEN ? THEN ? ".repeat(purchases.length);
let sql = `UPDATE product 
    SET amount_sold = amount_sold + 
        CASE sku_id ${whens} END
    WHERE sku_id IN (?)`;
let params = [],
  skus = [];
purchases.forEach(({sku, purchased}) => {
  params.push(sku, purchased);
  skus.push(sku);
});
params.push(skus);
db.query(sql, params, function(res, error) {
  // ...
});

answered Feb 12, 2021 at 21:29

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

1 Comment

DannyH Over a year ago

Sorry forgot to reply, works perfectly thank you!

Collectives™ on Stack Overflow

Adding multiple values to multiple rows in nodejs mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related