MySQL中通过SELECT结果执行DELETE操作报错求助

MySQL不允许在DELETE/UPDATE语句的FROM子句中直接引用正在被修改的目标表，这就是触发You can't specify target table 'customers' for update in FROM clause错误的原因。以下两种方法可以解决这个问题：

方法一：将子查询包装为派生表 #

把筛选未关联订单客户的子查询嵌套一层，生成临时结果集后再用于删除操作：

DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM (
        SELECT customer_id
        FROM customers 
        WHERE customer_id NOT IN (SELECT customer_id FROM orders)
    ) AS temp_customers
);

方法二：使用LEFT JOIN筛选删除 #

通过LEFT JOIN关联客户表和订单表，直接删除没有匹配订单记录的客户，这种方式性能更优：

DELETE c
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

同时完成删除与验证 #

如果要一次性完成删除并验证结果，可以连续执行以下语句：

-- 执行删除操作
DELETE c
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

-- 验证是否还存在未关联订单的客户
SELECT customer_id
FROM customers 
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

内容的提问来源于stack exchange，提问作者SeveN