mysqlmariadbmariasql

Can't use ALIAS in delete statement in MARIA DB

I am trying to delete dupes from a table but it's not letting me use alias. Tried various solutions given in several forums.

the query is, 

DELETE FROM `table` AS t1
        WHERE EXISTS (
         SELECT 1 FROM `table` AS t2
         WHERE t2.`col1` = t1.`col1`
         AND t2.`col2` = t1.`col2`
         AND t2.id > t1.id )
Solution

  • You can use a multi-table DELETE statement:

    DELETE t1 
FROM `table` t1
JOIN `table` t2 ON t2.col1 = t1.col1 AND t2.col2 = t1.col2 AND t2.id > t1.id