MySQL: How to do a conditional update?

Question

Hi,

I'm trying to a create an update statement along the following lines:

TABLE car: id | owner_id | type | status

An owner can have multiple cars.

UPDATE car c 
   SET c.type = 1 
     WHERE c.owner_id IN ($ids) 
     AND c.status = [1 IF THIS OWNER HAS A CAR WITH 1, ELSE A CAR WITH 0] // ... ???

$ids is reasonably small (under 50 values).

It seems simple but I can't seem to get my head around it because I can't use a SELECT subquery with an UPDATE to the same table.

Anyone?

Thanks

Answer 1

You say you have tried a query that fails but unfortunately you have not posted it, so I'm going to guess that it is this:

UPDATE car c 
SET c.type = 1 
WHERE c.owner_id IN ($ids) 
  AND c.status = (SELECT MAX(c2.status) FROM car c2 WHERE c.owner_id = c2.owner_id)

Posting your actual query would be helpful, but for now I'm just going to assume that this is what you tried. If you have something slightly different, it doesn't matter... the principle is the same. It fails as you as you correctly pointed out because you cannot update and select from the same table in one query. One way to workaround this error is to wrap the select in another select:

UPDATE car c 
SET c.type = 1 
WHERE c.owner_id IN ($ids) 
  AND c.status = (SELECT * FROM
                    (SELECT MAX(c2.status) FROM car c2 WHERE c.owner_id = c2.owner_id)
                  AS T1)

Surprisingly, this will work even though it seems that it should be equivalent to the first query. Note though that the reason why the original query doesn't work is because it doesn't correctly lock the table. This workaround tricks MySQL into allowing the query anyway, but you should be aware that it may be unsafe to use in a multi-user environment.