How to update multiple lines adding value in int column in MySQL?

Question

I have a PHP application which needs to update multiple product rows. This update should add a given number to the current value of the column called estoque.

Example: add 3 units to product A, 6 units to product B, etc.

Is there way to do this using only one query, like this?

insert into venda_produto (`cod`,`estoque`) values (49477,`estoque`+3), (98798,`estoque`+6) on duplicate key update `cod`=COALESCE( VALUES(`cod`), `cod`),`estoque`=COALESCE( VALUES(`estoque`), `estoque`)

`

So your key is cod and you want to increment estoque?

shmosel
– shmosel

2017-01-12 10:09:52 +00:00
Commented Jan 12, 2017 at 10:09 — shmosel
– shmosel, Commented Jan 12, 2017 at 10:09
Do you know if the rows exist yet?

shmosel
– shmosel

2017-01-12 10:10:57 +00:00
Commented Jan 12, 2017 at 10:10 — shmosel
– shmosel, Commented Jan 12, 2017 at 10:10

shmosel · Accepted Answer · 2017-01-12 10:12:20Z

This should do what you want:

INSERT INTO vendo_produto (cod, estoque) VALUES (49477, 3), (98798, 6) ON DUPLICATE KEY UPDATE estoque = estoque + VALUES(estoque);

Collectives™ on Stack Overflow

How to update multiple lines adding value in int column in MySQL?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related