Abhijith Nagaraja Abhijith Nagaraja -4 years ago 102
MySQL Question

Removing rows with duplicate values based on variable number of columns

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

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 Source

You can delete the duplicated rows

delete  my_table 
where id not in (( 
select min(id)  from my_table
group by column1, column2 ))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download