Deleting Duplicate Data In MySQL

For deleting duplicate rows in a MySQL table if we are writing the query as folows

 DELETE FROM table_name
where column_name in (
    SELECT  column_name
    FROM table_name
    GROUP BY column_A, column_B, column_C, ... 
    having COUNT(*)>1);

 then it will show

Error Code: 1093. You can't specify target table 'supplier_matrix' for update in FROM clause

It is required add a temporary table otherwise it will throw error.

Following query first selects the duplicate rows and creates a temporary table temp and the deletes the duplicate rows.

 

DELETE FROM table_name 
where column_name in(
    SELECT  column_name
        FROM (
                select column_name
                FROM table_name
                GROUP BY column_A, column_B, column_C, ...
                having COUNT(*)>1)temp
                );