1093 - You can‘t specify target table ‘xxx‘ for update in FROM clause

错误sql

UPDATE business_provider_order_air_extend poe
JOIN (
    SELECT id 
    FROM business_provider_order_air_extend 
    WHERE provider_order_id IN (
        SELECT provider_order_id 
        FROM business_provider_order_air_extend 
        WHERE provider_id = 2 
        AND fee <> 0
    ) 
    AND amount <> 0 
    AND fee <> 0
) t 
ON poe.id = t.id
SET poe.amount = poe.amount + poe.fee;
 

正确sql

UPDATE business_provider_order_air_extend
SET amount = amount + fee
WHERE id IN (
    SELECT id
    FROM (
        SELECT id 
        FROM business_provider_order_air_extend 
        WHERE provider_order_id IN (
            SELECT provider_order_id 
            FROM business_provider_order_air_extend 
            WHERE provider_id = 2 
            AND fee <> 0
        ) 
        AND amount <> 0 
        AND fee <> 0
    ) AS subquery
);