0

I am trying to execute this query. it is to delete all the accounts that is less than the average of all the accounts in the account table. I am however getting this error:

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

The query is:

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

How can I solve this problem?

Nicole Foster
  • 381
  • 4
  • 14

1 Answers1

1

Use JOIN:

DELETE a
FROM account a JOIN
     (SELECT AVG(balance) as avg_balance
      FROM account
     ) ab
     ON a.balance < ab.avg_balance;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786