CODEWITHSUNDEEP
mysql
Sasha
Sasha

Reputation: 8705

MySQL - increment values of several rows with one query

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?

Upvotes: 0

Views: 159

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

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)

Upvotes: 3

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

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'

Upvotes: 0

Related Questions