Clever Geek Handbook

MySQL UPDATE with subquery for null

I am trying to update the status of all planes using a subquery to "OUT" which are currently exiting and not returning. My foreign key is PLANE_NUM. I am trying to do it like this, but I have 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);
+3


source to share


3 answers


the problem you are having is that ==> MySQL does not allow you to reference the table to be updated in the 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 the links below and optimize as per your requirement

You cannot specify the target table to update in the FROM clause

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

+1


source


The best way to do this is by joining



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


source


Try it.

UPDATE airplane, plane, flight
SET airplane.STATUS='OUT'
WHERE airplane.PLANE_NUM = plane.PLANE_NUM
    AND plane.PLANE_NUM = flight.PLANE_NUM
    AND flight.LIGHT_RETURNDATE IS null
0


source






More articles:


All Articles