Delete duplicate rows on a huge list

Question

I have a huge list of roads and the place of that road, like below:

StreetName,PlaceName,xcoord,ycoord
Ovayok Road,Cambridge Bay,-104.99656,69.12876
Ovayok Road,Cambridge Bay,-104.99693,69.12865
Ovayok Road,Cambridge Bay,-104.99794,69.12842
Ovayok Road,Cambridge Bay,-104.99823,69.12835
Hikok Drive,Kugluktuk,-115.09433,67.82674
Hikok Drive,Kugluktuk,-115.09570,67.82686
Hikok Drive,Kugluktuk,-115.09593,67.82689
Hikok Drive,Kugluktuk,-115.09630,67.82695
Sivulliq Avenue,Rankin Inlet,-92.08252,62.81265
Sivulliq Avenue,Rankin Inlet,-92.08276,62.81265
Sivulliq Avenue,Rankin Inlet,-92.08461,62.81262

How to delete rows that have duplicates data on first and second column? All numbers (coordinates) are differents.

This is a duplicate item, please, check: [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — Angel M., Jan 30 '19 at 17:24
Possible duplicate of [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — Angel M., Jan 30 '19 at 17:24
@scaisEdge, I don't have unique ID. The xcoord and ycoord are unique — John, Jan 30 '19 at 17:29

score 0 · Answer 1 · answered Jan 30 '19 at 18:29

If you don't have any column by which you can uniquely identify your data or any column with ID, then fetch the unique records in the table and move them to a copy of the table and rename this temp table with the original table.

Below find the query for the same -

CREATE TABLE street_details_temp LIKE street_details; INSERT INTO street_details_temp SELECT DISTINCT * FROM street_details; DROP TABLE street_details; RENAME TABLE street_details_new TO street_details;

Delete duplicate rows on a huge list

1 Answers1