Delete rows with duplicate value in one column but different value in another column

Question

I have a table in MariaDB 5.5 that looks like this:

| PRODUCT_ID | STORE_ID |
-------------------------
|        111 |        0 |
|        111 |        1 |
|        112 |        0 |
|        112 |        1 |
|        113 |        0 |
|        114 |        1 |
|        115 |        1 |
|        116 |        0 |
|        117 |        0 |
|        117 |        1 |

What I want to do is to delete all duplicate rows by column product_id where value by store_id is 0 so the final table would look like this:

| PRODUCT_ID | STORE_ID |
-------------------------
|        111 |        1 |
|        112 |        1 |
|        113 |        0 |
|        114 |        1 |
|        115 |        1 |
|        116 |        0 |
|        117 |        1 |

This sql query returns all duplicate entries by column product_id:

SELECT `product_id` FROM `table` GROUP BY `product_id` HAVING COUNT(*) > 1

so I tried this one:

DELETE FROM `table` AS a WHERE a.`store_id` = '0' AND a.`product_id` IN (SELECT b.`product_id` FROM  `table` AS b GROUP BY b.`product_id` HAVING COUNT(*) > 1)

but it gives me an error in sql syntax:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS a WHERE a.`store_id` = '0' AND a.`product_id` IN (SELECT b.`product_id` FROM ' at line 1

What's the reason of this error and what query should I use instead?

Answer 1

This query will delete all rows that have STORE_ID=0 and that are duplicated:

DELETE t1.*
FROM
  yourtable t1 INNER JOIN (SELECT PRODUCT_ID
                           FROM yourtable
                           GROUP BY PRODUCT_ID
                           HAVING COUNT(*)>1) t2
  ON t1.PRODUCT_ID = t2.PRODUCT_ID
     AND t1.STORE_ID=0;

Please see fiddle here.