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开发者_如何学Go [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?

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).

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

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.

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;