CODEWITHSUNDEEP
mysqlsqlsql-updateconditional-statements
Nicolas Bouvrette
Nicolas Bouvrette

Reputation: 4747

MySQL Conditional Update in same table

I'm looking for a simple way to do an update on a table only if there is no other columns present in that same table with the same value I'm trying to update, ideally in a single query. So far I'm getting an error You specify target table 't1' for update in FROM clause. Here is what I tried in a few variations so far (still unable to get working):

UPDATE emailQueue AS t1 
SET 
    t1.lockedOn = 1470053240
WHERE
    (SELECT 
            COUNT(*)
        FROM
            emailQueue AS t2
        WHERE
            t2.lockedOn = 1470053240) = 0
        AND t1.lockedOn IS NULL

Upvotes: 0

Views: 348

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

In MySQL, you need to use a join. In this case, a left join is in order:

UPDATE emailQueue eq LEFT JOIN
       emailQueue eq2
       ON eq2.lockedOn = 1470053240
    SET eq.lockedOn = 1470053240
WHERE eq.lockedOn IS NULL AND
      eq2.lockedOn IS NULL;

Upvotes: 2

Related Questions