mysql - Having an SQL Error or I'm missing a syntax?

Question

I'm having a problem on using this SQL Query

DELETE FROM `acc_reg_num_db` t
WHERE EXISTS
       (SELECT 1 FROM `acc_reg_num_db` tt
         WHERE t.account_id = tt.account_id
           AND t.key = tt.key
           AND tt.value > t.value)
   AND t.key IN ('#betaminutes', '#online_minute')

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't WHERE EXISTS (SELECT 1 FROM acc_reg_num_db tt WHERE t.acco' at line 1

Can anyone help me with this please? Thanks

question from:https://stackoverflow.com/questions/65907139/having-an-sql-error-or-im-missing-a-syntax

Answer 1

MySQL requires that you list the table being deleted from. And, you cannot re-use the table being deleted from. So, try this:

DELETE arn
    FROM `acc_reg_num_db` arn JOIN
         (SELECT account_id, key, MAX(value) as max_value
          FROM acc_reg_num_db arn2
          GROUP BY account_id, key
         ) arn2
         USING (account_id, key)
    WHERE arn.value < arn2.max_value AND
          arn.key IN ('#betaminutes', '#online_minute');