MySQL delete from query where two columns are duplicate

What I'm looking to do, is delete all duplicated where column A and column B are duplicate. For example:

A      B      C   
---------开发者_StackOverflow社区--------
Apple  Pear   11  
Apple  Pear   12  
Apple  Pear   13  
Orange Apple  22  
Orange Beer   21  
Cinder Punch  30  
Cinder Punch  31  
Cinder Punch  32  

Would result in:

A      B      C   
-----------------
Apple  Pear   11  
Orange Apple  22  
Orange Beer   21  
Cinder Punch  30  

Step 1: Move the non duplicates (unique tuples) into a temporary table

CREATE TABLE new_table as
SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];

HERE, [column to remove duplicates by] = column names seperated by "COMMA", so in your case A,B

Step 2: delete delete the old table We no longer need the table with all the duplicate entries, so drop it!

DROP TABLE old_table;

Step 3: rename the new_table to the name of the old_table

RENAME TABLE new_table TO old_table;

you can do as follow to achive this

delete from table_name
where rowid not in (select min(rowid) from table_name group by a,b)