MySQL UPDATE with subquery for null

Question

I'm trying to update the status of all airplanes, using a subquery, to 'OUT' who are currently out and have not returned. My foreign key is PLANE_NUM. I'm trying it like this but I've got an error:

UPDATE plane
SET STATUS='OUT'
WHERE PLANE_NUM 
IN (SELECT *
    FROM plane p, flight f
    WHERE p.PLANE_NUM = f.PLANE_NUM
    AND FLIGHT_RETURNDATE IS null);

Answer 1

A better way of doing is by joining as

update plane p
left join flight f
on p.PLANE_NUM = f.PLANE_NUM
SET p.STATUS='OUT'
where f.FLIGHT_RETURNDATE IS null ;

Answer 2

problem you are facing is because ==> MySQL doesn’t allow referring to a table that’s targeted for update in a FROM clause, which can be frustrating.

This will work for you

UPDATE plane
SET STATUS='OUT'
WHERE PLANE_NUM 
IN (SELECT * FROM (select p.PLANE_NUM
    FROM plane p, flight f
    WHERE p.PLANE_NUM = f.PLANE_NUM
    AND FLIGHT_RETURNDATE IS null) as B );

Not Optimized. Please refer to links below and optimize as per your requirement

You can't specify target table for update in FROM clause

http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/