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);

In your sub query, you are selecting `*` that is all fields. Should you not only be selecting the `PLANE_NUM` that is the corresponding field in your sub query ? — Noman Ur Rehman, Apr 27 '15 at 08:04
Error Code: 1093. You can't specify target table 'plane' for update in FROM clause — alcor8, Apr 27 '15 at 08:17

score 2 · Answer 1 · answered Apr 27 '15 at 08:26

2

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 ;

answered Apr 27 '15 at 08:26

Abhik Chakraborty

44,654
6
52
63

This one doesn't work.This query comes back with planes who don't have a flight as well as the ones whose FLIGHT_RETURNDATE IS null – alcor8 Apr 27 '15 at 08:45
Make it `inner join` instead of `left join` – Abhik Chakraborty Apr 27 '15 at 08:48

score 1 · Accepted Answer · edited May 23 '17 at 12:06

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/

juntapao · Answer 3 · 2015-04-27T09:03:35.327

0

Try this one.

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

edited Apr 27 '15 at 09:03

answered Apr 27 '15 at 08:18

juntapao

407
3
12

This query throws: Error Code: 1093. You can't specify target table 'plane' for update in FROM clause – alcor8 Apr 27 '15 at 08:45
Try this query: UPDATE airplane, plane, flight SET airplane.STATUS='OUT' WHERE airplane.PLANE_NUM = p.PLANE_NUM and p.PLANE_NUM = f.PLANE_NUM and LIGHT_RETURNDATE IS null – juntapao Apr 27 '15 at 08:59

MySQL UPDATE with subquery for null

3 Answers3