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

Popular posts from this blog

apache - Add omitted ? to URLs -

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

php - How can I stop spam on my custom forum/blog? -