CODEWITHSUNDEEP
phpmysqlsql
Sean Bone
Sean Bone

Reputation: 3556

SQL SELECT then UPDATE in one query

I have to randomly select a row from a MySQL DataBase, then increment by one a counter on that row (same table). At the moment, I'm running

SELECT id, img, title, alt, ... FROM table WHERE ... ORDER BY RAND() LIMIT 1

and then separately

UPDATE table SET counter = counter + 1 WHERE id = $id

Thus running two queries. Is there a way to do this all in one query? Thanks!

Upvotes: 0

Views: 763

Answers (1)

Kickstart
Kickstart

Reputation: 21533

If you only need to increment the value and not bring back a row to process you could do the following:-

UPDATE fred
INNER JOIN
(
    SELECT id
    FROM fred
    ORDER BY RAND()
    LIMIT 1
) sub0
ON fred.id = sub0.id
SET fred.counter = fred.counter + 1

Or simpler:-

UPDATE fred
SET fred.counter = fred.counter + 1
ORDER BY RAND()
LIMIT 1

Upvotes: 1

Related Questions