I was trying to figure out a query to count the IP addresses I had in a table, tell me how many are the same, then delete duplicates only leaving a single record for each.

Data example:
id, ip
1     72.135.77.1
2     25.487.24.8
3     25.781.67.4
4     25.781.67.4
5     72.135.77.1

If you use this example, of course always backup your table first or better yet do this on development.

This statement deletes all duplicate ip rows but leaves 1 row with the highest id for each IP address.

delete ip1 from influencerIPs ip1, influencerIPs ip2
where ip1.id < ip2.id and ip1.ip = ip2.ip

While this statement deletes all duplicate ip rows but leaves 1 row with the lowest id for each IP address, if that matters to you.

delete ip1 from influencerIPs ip1, influencerIPs ip2
where ip1.id > ip2.id and ip1.ip = ip2.ip

Leave a Reply