1

I noticed that I have a lot of duplicates in one of my tables. Here's an example of how the table looks (example with one id_product_attribute number):

enter image description here

So I want to delete the duplicate currencies for EACH id_product_attribute. The desired result is to have one id currency for each id_product_attribute. Which means: 1, 2, 3, 4, 6 --> 5 rows per id_product and not 10 (5 duplicates).

I tried to use different codes but I can't make it work:

DELETE FROM product_attribute USING product_attribute, product_attribute pa
WHERE product_attribute.id_currency > pa.id_currency
AND product_attribute.id_product_attribute = pa.id_product_attribute
AND product_attribute_price.id_product_attribute = '16632'

So If I run the above, I will delete everything and only id_curreny 1 will stay there. I need to have:

Attribute Currency 
  16632    -    1
  16632    -    2
  16632    -    3
  16632    -    4
  16632    -    6

Can anyone help me with this? Any hint would be much appreciated.

Thank you

shieldcy
  • 592
  • 2
  • 11
  • 35
  • See this : http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql or http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql – PaulF Sep 04 '15 at 10:23

1 Answers1

1

Personally, I would use a subquery with aggregation. But you can also take the approach that you want. Instead of a regular join, use a left join and check for no matches:

DELETE pa
    FROM product_attribute pa LEFT JOIN
         product_attribute pa2
         ON product_attribute.id_currency > pa.id_currency AND
            pa.id_product_attribute = pa2.id_product_attribute AND
    WHERE pa.id_product_attribute = '16632' AND
          pa2.id_product_attribute IS NULL;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hello, thanks for your answer. I am currently getting a syntax error "Error in query (1064): Syntax error near 'pa LEFT JOIN product_attribute pa2 ON produ' at line 2 ". Will try to fix that and see if it works :) Thanks again – shieldcy Sep 04 '15 at 10:47