CODEWITHSUNDEEP
mysqlsqlcountsql-deletehaving-clause
hagu81
hagu81

Reputation: 197

mySQL delete row with count(*) GROUP and HAVING

I have this query where i get all double entries with the same articleID and the categoryID = 2153 Now i want to delete this rows. But this seems not to work directly. So i tried it with subquery, but when i use IN i would need a subquery which returns only the id. But that's also not possible.

How can i delete the rows from this query?

SELECT id, articleID, categoryID, count(*) AS count 
FROM `s_articles_categories`
GROUP BY articleID
HAVING count(*) > 1 AND categoryID = 2153

Upvotes: 0

Views: 803

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

I would recommend writing this as:

delete ac
    from s_articles_categories ac join
         (select articleId, categoryid, min(id) as min_id
          from s_articles_categories
          where categoryID = 2153
          group by articleId, categoryid
         ) ac2
         on ac2.articleId = ac.articleId and
            ac2.categoryid = ac.categoryid and
            ac.id > ac2.min_id

Upvotes: 0

GMB
GMB

Reputation: 222682

If you want to delete all duplicates but keep one of them in each group, then you can use the following query :

DELETE FROM `s_articles_categories` s
WHERE s.categoryID = 2153 AND EXISTS (
    SELECT 1 
    FROM `s_articles_categories` s1
    WHERE s1.articleID = s.articleID AND s1.categoryID = s.categoryID AND s1.id < s.id
)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

Assuming that the id is the primary key for the table s_articles_categories You could use a join on the subselect result.

To delete all rows :

delete  r.* 
from s_articles_categories r
INNER JOIN (
    SELECT id, articleID, categoryID, count(*) AS count FROM
    `s_articles_categories` r
    GROUP BY articleID
   HAVING count(*) > 1 AND categoryID = 2153

) t on t.id = r.id

Upvotes: 3

Related Questions