Search code examples
mysqlsqljoincountsql-delete

MYSQL Delete Query based on condtion

I have a table Product and i have another table Rating I want to delete products that have 3 or more ratings score lower than 3.

Here's what i tried

DELETE p  FROM product p 
JOIN rating r ON r.produits_id = p.id  
IN
  (SELECT produits_id 
  FROM rating 
WHERE rating.score < 3
GROUP BY rating.produits_id  
HAVING COUNT(*)  > 2 )
Solution

  • You should join the table directly to the query that returns the ids from rating:

    DELETE p  
FROM product p 
JOIN (
  SELECT produits_id 
  FROM rating 
  WHERE score < 3
  GROUP BY produits_id  
  HAVING COUNT(*)  > 2
) r ON r.produits_id = p.id