MYSQL - 您不能在 FROM 子句中指定要更新的目标表“包” - MYSQL - You can't specify target table 'packs' for update in FROM clause

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

我有以下查询：

UPDATE packs set `LastmovementTypeID`=9 WHERE `ShipmentID` IN (SELECT ShipmentID FROM packs packs2 WHERE (packs2.TaskMarker = '2' AND packs2.Movementtype != 'Bezorgscan depot' AND packs2.LastmovementTypeID != 0) UNION SELECT ShipmentID FROM packs packs3 WHERE (packs3.TaskMarker = '3' AND packs3.LastmovementTypeID != 0))

当我执行它时，我得到结果

#1093 - You can't specify target table 'packs' for update in FROM clause

这是什么原因呢？因为我要更新的是同一表中的记录，其中在查询返回的列表中找不到我的 ShippingID：

SELECT ShipmentID FROM packs packs2 WHERE (packs2.TaskMarker = '2' AND packs2.Movementtype != 'Bezorgscan depot' AND packs2.LastmovementTypeID != 0) UNION SELECT ShipmentID FROM packs packs3 WHERE (packs3.TaskMarker = '3' AND packs3.LastmovementTypeID != 0)

正如 http://dev.mysql.com/doc/refman/5.5/en/update.html 中所述："目前，您无法更新表并从子查询中的同一表中进行选择。

您需要设计一个解决方法 - 例如，选择到临时表中，然后从那里更新。

你可以用这样的东西绕过这个约束：

UPDATE packs set `LastmovementTypeID` = 9 
WHERE `ShipmentID` IN 
    (SELECT ShipmentID FROM 
        (SELECT ShipmentID FROM packs packs2 
            WHERE (packs2.TaskMarker = '2' AND packs2.Movementtype != 'Bezorgscan depot' AND packs2.LastmovementTypeID != 0) UNION SELECT ShipmentID FROM packs packs3 WHERE (packs3.TaskMarker = '3' AND packs3.LastmovementTypeID != 0)
        ) AS `x`
     );