CODEWITHSUNDEEP
mysqlsqljoincountsql-delete
Abdelmlak Dhif
Abdelmlak Dhif

Reputation: 479

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 )

Upvotes: 1

Views: 34

Answers (2)

forpas
forpas

Reputation: 164214

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You can just use in in the where clause:

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

Upvotes: 0

Related Questions