CODEWITHSUNDEEP
phpmysqlsql
themerlinproject
themerlinproject

Reputation: 3582

Update a single mysql row if another row hasn't been updated

Let's say I have the following select statement, which returns the first occurrence where dog=0 after the insert_time:

SELECT mammal, id
FROM mytable
WHERE dog = 0 
  AND insert_time > "2012-02-09 00:00:00"
LIMIT 0, 1

I then want to update that row so that mammal = 1 ONLY if mammal doesn't already =1. The only way that I would currently know how to do this would be to fetch the array in PHP, check the mammal element and then run another mysql statement to UPDATE the row.

UPDATE mytable
SET mammal = 1
WHERE id = selectidfromabove

Is there a way I can do this all in one mysql statement, perhaps wrapping the SELECT statement above in an UPDATE statement?

Upvotes: 0

Views: 72

Answers (1)

Daniel Miladinov
Daniel Miladinov

Reputation: 1592

You can do exactly what you proposed:

UPDATE mytable
SET mammal=1
WHERE id IN (
    SELECT id
    FROM mytable
    WHERE dog = 0 
        AND insert_time > "2012-02-09 00:00:00"
    LIMIT 0, 1
);

Note: your original select query was modified to only select id.

Upvotes: 3

Related Questions