Msg#: 4669249 posted 4:26 pm on May 7, 2014 (gmt 0)
First, let's look at the correct query you should be using to identify the data you want to keep (you can use MIN instead of MAX- it works either way): SELECT product_code, attibute, MAX(id) AS keep_id FROM $tablename GROUP BY product_code, attibute
This gives you each unique product_code/attibute pair, plus an associated id (keep_id). To remove dupes, delete everything in the table that doesn't belong to one of those ids: DELETE FROM $tablename WHERE id NOT IN (SELECT MAX(id) FROM $tablename GROUP BY product_code, attibute)
Msg#: 4669249 posted 12:01 am on May 11, 2014 (gmt 0)
If your running this query because the table shouldn't contain duplicate values then you should prevent this from happening in the first place by: a) Preventing it by using a unique index/key non the table. b) Testing for existing data before trying to insert duplicate values.
It also sounds like you should be using 2 tables here, one for product codes and another for product/attributes.