double - dejurin/blog GitHub Wiki

INSERT INTO addresses1 (city_id, locality, street, building, view, lat, lng, pubdate) SELECT city_id, locality, street, building, view, lat, lng, pubdate FROM addresses WHERE city_id = 1 GROUP BY street, building HAVING COUNT(*) > 1

SELECT city_id, locality, street, building, view, lat, lng, pubdate,COUNT() FROM addresses WHERE city_id = 1 GROUP BY street, building HAVING COUNT() > 1

INSERT IGNORE INTO addresses1 (city_id, locality, street, building, view, lat, lng, pubdate) SELECT city_id, locality, street, building, view, lat, lng, pubdate FROM addresses WHERE city_id = 1

SELECT city_id, locality, street, building, view, lat, lng, pubdate, COUNT() FROM addresses1 WHERE city_id = 1 GROUP BY street, building HAVING COUNT() > 1

SELECT * FROM addresses INNER JOIN (SELECT Min(id) minid, street, building FROM addresses GROUP BY street, building HAVING Count(1) > 1) AS duplicatesTable ON ( duplicatesTable.street = addresses.street AND duplicatesTable.building = addresses.building AND duplicatesTable.minid <> addresses.id )