MySQL UPDATE on SELECT

Question

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

For eg., If the query

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

Return two rows, then on the same query, i need to increment the table's count field by 1. Is it possible?

@krammer I need to update the selected rows count by 1(to get the number of times it is displayed) after selecting the data. So i think it will better to write both the query as one — Stranger
– Stranger, Commented Jul 26, 2012 at 10:11
Why don't you write two seperate queries? The one you are asking for is not supported in single query — Madhivanan
– Madhivanan, Commented Jul 26, 2012 at 10:18
@Udhay: I don't think MySQL has anything similar to PostgreSQL's UPDATE ... RETURNING. You will have to write two queries. — user330315
– user330315, Commented Jul 26, 2012 at 10:19

rkosegi · Accepted Answer · 2012-07-26 10:41:21Z

5

Yes its possible you can write as UPDATE query as:

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

or for LIMIT with offset try:

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;

edited Jul 26, 2012 at 10:41

rkosegi

14.8k5 gold badges57 silver badges90 bronze badges

answered Jul 26, 2012 at 10:05

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

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

Comments

eggyal · Accepted Answer · 2012-07-26 10:20:33Z

It's not possible. The verb SELECT only retrieves data (without modifying it); and the verb UPDATE only modifies data (without retrieving it). There is no MySQL verb that will perform both actions. You will have to use two separate statements.

However, those two statements can be encapsulated within a transaction (if supported by your storage engine) to ensure that they are conducted atomically and/or could be invoked from within a stored procedure to simplify the command that must be issued by your client. Combining the two one would have:

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 need merely do:

CALL select_and_update('value');

Collectives™ on Stack Overflow

MySQL UPDATE on SELECT

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related