MySQL - increment values of several rows with one query

Question

Is it possible to update and increment values of several rows with one query?

I have a following table services:

id | name | count | created_at | updated_at

I want to update the count every time a user chooses some of the services in the search (this is done via GUI, and the user can choose one or more services, and I am getting the ids of the services).

I can perform a single query for each service when more than one is selected, but that doesn't seems to be very efficient.

Example:

I need to update 7 services at once and increment the count value for each of them:

UPDATE services SET count = count + 1 WHERE id = 28

UPDATE services SET count = count + 1 WHERE id = 29

UPDATE services SET count = count + 1 WHERE id = 39

UPDATE services SET count = count + 1 WHERE id = 44

UPDATE services SET count = count + 1 WHERE id = 296

UPDATE services SET count = count + 1 WHERE id = 294

UPDATE services SET count = count + 1 WHERE id = 111

Is it possible to perform this update in one query?

Not sure i have a feeling you are looking for this UPDATE table SET count = count + 1 WHERE .... .. But hard to say without more details.. Read this stackoverflow.com/help/how-to-ask section "Help others reproduce the problem" — Raymond Nijland
– Raymond Nijland, Commented Sep 10, 2018 at 12:11
The question seem a bit broad. Depending what other language you substitute your SQL with, then yes, it is possible. You could use a loop to update your rows based on the ID's. — Martin
– Martin, Commented Sep 10, 2018 at 12:11

Raymond Nijland · Accepted Answer · 2018-09-10 12:18:27Z

3

i need to update 7 services at once and increment the count value for each of them:

UPDATE services SET count = count + 1 WHERE id = 28

UPDATE services SET count = count + 1 WHERE id = 29

UPDATE services SET count = count + 1 WHERE id = 39

UPDATE services SET count = count + 1 WHERE id = 44

UPDATE services SET count = count + 1 WHERE id = 296

UPDATE services SET count = count + 1 WHERE id = 294

UPDATE services SET count = count + 1 WHERE id = 111

Is it possible to perform this update in one query?

Can be rewritten into one query.

Query

UPDATE 
 services 
SET 
 count = count + 1 
WHERE
 id IN(28, 29, 39, 44, 296, 294, 111)

answered Sep 10, 2018 at 12:18

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

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

Comments

Suresh Kamrushi · Accepted Answer · 2018-09-10 12:11:13Z

0

You can try like this:

update table set `count` = `count` + 1

Above query will update all rows.
If you want to update specific row you can add where condition:

update table set `count` = `count` + 1 where name = 'searchText'

answered Sep 10, 2018 at 12:11

Suresh Kamrushi

16.2k13 gold badges79 silver badges93 bronze badges

4 Comments

Sasha Over a year ago

I can do that for a single row, but that is a single query. I want to avoid making ten queries (if ten services are selected).

Suresh Kamrushi Over a year ago

You can have where condition to select 10 services at once

Suresh Kamrushi Over a year ago

Please share sample data for more specific answer

Sasha Over a year ago

@RaymondNijland - this is exactly what I need. Didn't know I can use WHERE IN within update. Thanks mate :)

Collectives™ on Stack Overflow

MySQL - increment values of several rows with one query

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related