0

I have certain geocoded addresses that are duplicates in my SQL table I am trying to delete and only leave one. I've written the code below:

SELECT
address`, COUNT(*)
FROM stores
GROUP BY  address`
HAVING COUNT(*) > 1

However, it only shows the address and the actual count (which is 2) showing duplicates and won't let me select any to delete. What additional sql command do I need to add to delete duplicates?

jophab
  • 5,356
  • 14
  • 41
  • 60
chronotrigga
  • 589
  • 1
  • 10
  • 33
  • This post is talking about removing duplicate rows in SQL - http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows – Wasi Ahmad Oct 26 '16 at 21:21
  • Possible duplicate of [Delete rows from table, except specified number (limit number of rows)](http://stackoverflow.com/questions/24527833/delete-rows-from-table-except-specified-number-limit-number-of-rows) – Christine Oct 26 '16 at 21:32
  • You can use a LIMIT clause in DELETE statements; depending on how you are going to execute the DELETEs you could just construction them with a limit of the retrieved count - 1. – Uueerdo Oct 26 '16 at 21:41

1 Answers1

0

Assuming that id is unique column in your table stores

   DELETE FROM stores WHERE id NOT IN 
    ( SELECT min FROM (SELECT MIN(id) AS min FROM stores GROUP BY address) AS T1)
jophab
  • 5,356
  • 14
  • 41
  • 60
  • Unfortunately it seems like no rows were deleted. Can you explain the T1? – chronotrigga Oct 27 '16 at 14:48
  • @chronotrigga there was a mistake in query. its corrected. Check it now. T1 is used to avoid the error 1093 of mysql. Because we cannot use target table for delete. – jophab Oct 27 '16 at 15:14