mysql - 从表中删除数据时出现 sql 错误 - 删除的目标和源

Question

我正在尝试执行此查询。就是删除所有小于account表中所有账户平均值的账户。但是，我收到此错误：

#1093 - Table 'account' is specified twice, both as a target for 'DELETE' and as a separate source for data

查询是：

DELETE 
FROM account WHERE account_number IN 
(
  SELECT account_number FROM account 
WHERE balance < (SELECT AVG (balance) FROM account)
);

我怎么解决这个问题？

标签： mysqlsqldatabase

Answer 1

使用JOIN：

DELETE a
FROM account a JOIN
     (SELECT AVG(balance) as avg_balance
      FROM account
     ) ab
     ON a.balance < ab.avg_balance;