MySQL更新，子查询为null

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:

我正在尝试使用子查询更新所有飞机的状态，以“OUT”出当前未返回的飞机。我的外键是PLANE_NUM。我试着这样做但是我有一个错误

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 个解决方案

#1

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.

您所面临的问题是，因为==> MySQL不允许引用针对FROM子句中的update的表，这可能会令人沮丧。

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

不能为FROM子句中的update指定目标表

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

#2

A better way of doing is by joining as

更好的做法是加入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 ;

#3

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

#1