CODEWITHSUNDEEP
sqlmysql
Daniel
Daniel

Reputation: 1062

Deleting records using delete/where exists problem

My problem is I want to delete records from two tables, deleting the records that are a result of the following query:

SELECT AVG(pr.rating) AS rating_average
FROM products p 
INNER JOIN product_ratings pr 
ON pr.product_id = p.product_id  
GROUP BY p.product_id 
HAVING AVG(pr.rating) < 3

The above query shows average ratings of a product that are less than 3, I want to delete products and their associated ratings of all the results from the above query.

I looked at DELETE FROM product_ratings, products WHERE EXISTS (//above query), but this didn't work, I've been trying various DELETE statements to no avail.

I have read the following, and still cannot find a solution: SQL: DELETE Statement & SQL: EXISTS Condition.

The tables are products and product_ratings, with the following structure:

products
--------
product_id [PK] | link | ...

product_ratings
---------------
rating_id [PK] | rating | product_id

Appreciate any help, as well as links to reference material to better understand how it's done.

EDIT: Apologies for not stating what RDBMS I'm using, It's MySQL

EDIT2: A bit confused now, @Martin's example doesn't use a temp table like the other answers, I assume this is because of my vague question not stating with RDBMS I was using?

Upvotes: 0

Views: 4217

Answers (4)

Martin Smith
Martin Smith

Reputation: 453243

You have now added the MySQL tag. In that case this might do the job for you.

DELETE products,
       product_ratings
FROM   products,
       product_ratings
WHERE  product_ratings.product_id = products.product_id
       AND product_ratings.product_id IN 
 (SELECT product_id
        FROM   (SELECT p.product_id
                FROM   products p
                       LEFT JOIN product_ratings pr
                         ON pr.product_id = p.product_id
                GROUP  BY p.product_id
                HAVING COALESCE(AVG(pr.rating), 0) < 3) T)

MySQL does support a multiple table DELETE syntax. The derived table is to get around the issue where it doesn't allow mutating (update or delete target) tables to be referenced in a sub query (it materializes the result into a temporary table).

Upvotes: 2

Luke Woodward
Luke Woodward

Reputation: 64959

If your DBMS supports it, you could add a foreign key-constraint with an ON DELETE CASCADE clause to the product_ratings table, for example:

ALTER TABLE product_ratings ADD CONSTRAINT fk_prodratings_prods 
  FOREIGN KEY (product_id) REFERENCES products(product_id)
  ON DELETE CASCADE;

Once you've added this constraint, you only need to DELETE from the products table. The ON DELETE CASCADE constraint on the constraint above will then delete the ratings for you, e.g.

DELETE FROM products p 
 WHERE (SELECT AVG(pr.rating)
          FROM product_ratings pr
         WHERE pr.product_id = p.product_id) < 3;

Upvotes: 0

Steve Wellens
Steve Wellens

Reputation: 20620

You should create a foriegn key with a referential constraint between the tables.

You can make it do a cascading delete automatically (when a parent record is deleted, the child records will be automatically deleted).

http://en.wikipedia.org/wiki/Foreign_key#CASCADE

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Add the p.product_id to your select statement and store the results of that query in a temporary table. Then use that temporary table to delete from product_ratings and products in two separate delete operations.

The specific syntax of the delete will depend on which RDBMS you're using.

Upvotes: 1

Related Questions