sql - Deleting records using delete/where exists problem -
my problem want delete records 2 tables, deleting records result of following query:
select avg(pr.rating) rating_average products p inner join product_ratings pr on pr.product_id = p.product_id group p.product_id having avg(pr.rating) < 3
the above query shows average ratings of product less 3, want delete products , associated ratings of results above query.
i looked @ delete product_ratings, products exists (//above query)
, didn't work, i've been trying various delete statements no avail.
i have read following, , still cannot find solution: sql: delete statement & sql: exists condition.
the tables products
, product_ratings
, following structure:
products -------- product_id [pk] | link | ... product_ratings --------------- rating_id [pk] | rating | product_id
appreciate help, links reference material better understand how it's done.
edit: apologies not stating rdbms i'm using, it's mysql
edit2: bit confused now, @martin's example doesn't use temp table other answers, assume because of vague question not stating rdbms using?
you have added mysql
tag. in case might job you.
delete products, product_ratings products, product_ratings product_ratings.product_id = products.product_id , product_ratings.product_id in (select product_id (select p.product_id products p left join product_ratings pr on pr.product_id = p.product_id group p.product_id having coalesce(avg(pr.rating), 0) < 3) t)
mysql does support multiple table delete syntax. derived table around issue doesn't allow mutating (update or delete target) tables referenced in sub query (it materializes result temporary table).
Comments
Post a Comment