MySQL Conditional Update in same table

Question

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

Possible duplicate of how to update database sequentially using mysql — e4c5
– e4c5, Commented Aug 1, 2016 at 12:30
You have to use a join as mentioned in that question and as posted by @Gordon-linoff as an answer — e4c5
– e4c5, Commented Aug 1, 2016 at 12:34

Gordon Linoff · Accepted Answer · 2016-08-01 12:33:29Z

2

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;

answered Aug 1, 2016 at 12:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Nicolas Bouvrette Over a year ago

I'm not sure I understand exactly how this works but it works somehow. More explanation in the answer could be useful :)

Collectives™ on Stack Overflow

MySQL Conditional Update in same table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related