Removing rows with duplicate values based on variable number of columns

Question

Consider the following table

---

column1 | column2 | column3 | column4

a | b | 1 | 2
a | b | 2 | 3
a | c | 2 | 4

I want remove duplicates based on cloumn1 and column2 and keep only one such instance. Therefore the above table should look something like

a | b | 1 | 2
a | c | 2 | 4

I tried to Add Unique index like this

ALTER IGNORE TABLE `my_table` 
ADD UNIQUE INDEX `index1` (`column1` ASC, `column2` ASC);

This should have worked perfectly but

As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.

So what are the alternatives for this scenario?

NOTE: This is not a duplicate question. I have searched before asking this. Please read in once again before marking as a duplicate

Answer 1

You can delete the duplicated rows

delete  my_table 
where id not in (select t.id from ( 
select min(id)  as id from my_table
group by column1, column2 ) as t )