We have several duplicate Stores in our DB that need to be removed and we identify dupes by using the Store's latitude.
In this example of the Producer table, we would want to remove producerID 123 (our Production Table has 10K rows).
+------+--------+------+------+---------+--------+
| producerID | producerName | type | lat |
+------+--------+------+------+---------|--------|
| 123 | ToysRUs | Retail | 52.445 |
| 124 | Toys R Us | Online | 52.445 |
| 234 | GameStop | Retail | 55.150 |
| 345 | Amazon | Online | 56.110 |
+------+--------+------+------+---------+--------|
*Store is the same as Producer
This query gives us all the producerIDs that have the same lat value.
SELECT COUNT(*) AS 'TotalStores', GROUP_CONCAT(producerID, SEPARATOR ',') AS 'DupeStores'
FROM Producer
GROUP BY lat
HAVING TotalStores > 1
ORDER BY TotalStores DESC
+------+--------+------+----+
| TotalStores | DupeStores |
+------+--------+------+----+
| 3 | 123,124,125 |
| 3 | 555,556,557 |
| 2 | 666,665 |
| 2 | 777,787 |
+------+--------+------+----+
Then, we take those producerIDs (copy/paste) and run another query to remove those duplicates based on the type. Anything that has a duplicate AND has a type of 'Retail', we want to delete.
DELETE FROM Producer WHERE type = 'Retail'
AND storeID IN (123, 124, 125, 555, 556, 557, 666, 665, 777, 787)
How can we combine these into one query? When I try this, I get an error:
Operand should contain 1 column(s)
DELETE FROM Producer WHERE type = 'Retail'
AND producerID IN (SELECT COUNT(*) AS 'TotalStores', GROUP_CONCAT(producerID SEPARATOR ',')
FROM Producer
GROUP BY lat
HAVING TotalStores > 1)