MySQL UPDATE on SELECT

Question

In MySQL, is it possible to update selected records on the same query?

For example, if the request

SELECT * FROM `table` WHERE field = "value" LIMIT 0,2 

Return two rows, then for the same query I need to increase the count field of the table by 1. Is this possible?

Answer 1

It's impossible. The verb SELECT only retrieves data (without modifying it); and the verb UPDATE changes only the data (without receiving it). There is no MySQL verb that will do both things. You will need to use two separate operators.

However, these two statements can be encapsulated in a transaction (if supported by your storage engine) to ensure that they are executed atomically and / or can be called from a stored procedure to simplify the command that should be issued by your client. Combination of two:

 DELIMITER ;; CREATE PROCEDURE select_and_update(value TEXT) BEGIN START TRANSACTION; SELECT * FROM `table` WHERE field = value LIMIT 0,2; UPDATE `table` SET count = count + 1 WHERE ...; COMMIT; END;; DELIMITER ; 

Then your client just needs to:

 CALL select_and_update('value'); 

Answer 2

Yes, it is possible, you can write as an UPDATE query like:

 UPDATE my_table SET count = count + 1 WHERE field = "value" LIMIT 2; 

or for LIMIT with a try offset:

 UPDATE my_table a INNER JOIN (SELECT id FROM my_table WHERE field = "value" LIMIT 0, 2) b ON a.id = b.id SET count = count + 1;