CODEWITHSUNDEEP
mysqlsqlsql-delete
mukama
mukama

Reputation: 979

MySQL DELETE statement with a join, HAVING and GROUP BY

I have a table of items and another of reports. Each report has a foreign key linking to an item being reported.

I am trying to delete all items displayed in this query:

SELECT items.id, title, SUM(weight) AS total_weight, SUM(weight)*10/views AS score 
FROM items, reports 
WHERE items.id = reports.item_id 
GROUP BY items.id 
HAVING score >= 50;

Trying something like this:

DELETE items 
FROM (SELECT items.id, title, SUM(weight) AS total_weight, SUM(weight)*10/views AS score 
    FROM items, reports 
    WHERE items.id = reports.item_id 
    GROUP BY items.id 
    HAVING score >= 50)
AS T;

Gives me this error message:

ERROR 1109 (42S02): Unknown table 'items' in MULTI DELETE

Upvotes: 3

Views: 6458

Answers (4)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

DELETE FROM items 
WHERE id IN (SELECT id 
             FROM (SELECT i.id itemId, (SUM(weight) * 10 / views) score
                   FROM items i INNER JOIN reports r ON i.id = r.item_id 
                   GROUP BY itemId HAVING score >= 50
                  ) AS A
            );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

In MySQL, you have to be careful about the subqueries. I think the following works:

DELETE FROM items 
WHERE id IN (select *
             from (SELECT items.id 
                   FROM items join reports 
                        on items.id = reports.item_id 
                   GROUP BY items.id 
                   HAVING SUM(weight)*10/views >= 50
                  )
            )

It tricks the compiler into accepting the query by using an additional subquery. I also fixed your join syntax.

The following, though, rewrites the query into a more common syntax, using a correlated subquery:

delete from items
where exists (select r.item_id
              from reports r
              where r.item_id = items.item_id 
              group by r.item_id
              having SUM(r.weight)*10/items.views >= 50
             )

This is guessing that weight and views come from reports. Otherwise, you need to put theitems` alias in front instead.

Upvotes: 4

Ravi
Ravi

Reputation: 31417

I believe your delete statement is wrong. It should be delete from tablename where [condition].

    DELETE FROM items 
    WHERE 
      id IN (
        Select T.id from (SELECT items.id, title, SUM(weight) AS total_weight, SUM(weight)*10/views AS score 
FROM items, reports 
WHERE items.id = reports.item_id 
GROUP BY items.id 
HAVING score >= 50) T)

Upvotes: 2

GolezTrol
GolezTrol

Reputation: 116110

DELETE FROM items 
WHERE 
  id IN (
    SELECT 
      items.id 
    FROM items, reports 
    WHERE items.id = reports.item_id 
    GROUP BY items.id 
    HAVING SUM(weight)*10/views >= 50)

Upvotes: 4

Related Questions