Abhijith Nagaraja Abhijith Nagaraja - 3 months ago 8
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

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

You can delete the duplicated rows

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