mysql syntax error on removing duplicates

Question

I am trying to remove rows having duplicate hid column values with this query:

DELETE FROM dmf_product_match_unmatches as pmu1
WHERE ID not in 
(
    SELECT MAX(ID) as maxRecId
        FROM dmf_product_match_unmatches as pmu2
        GROUP BY hid       
);

But this gives

QL Error [1064] [42000]: 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 'as pmu1
WHERE ID not in 
(
    SELECT MAX(ID) as maxRecId
        FROM dmf_prod' at line 1

The query looks fine. What am I missing here?

Answer 1

This is the case when the error message does not match the error by some unknown reason. I don't understand why so - while executing in CLI the error message must be

You can't specify target table 'pmu1' for update in FROM clause

The root of the error - you cannot use the table to be altered as datasource of the subquery in WHERE.

You must use multiple-table DELETE syntax instead:

DELETE pmu1
FROM dmf_product_match_unmatches as pmu1
LEFT JOIN ( SELECT MAX(ID) as ID, hid
            FROM dmf_product_match_unmatches as pmu2
            GROUP BY hid) pmu3 USING (ID, hid)
WHERE pmu3.ID IS NULL;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=202cddc698cda3f83e0172c7e4e28a7d