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
