CODEWITHSUNDEEP
sqldatabasemysql
Hossein
Hossein

Reputation: 41961

MySQL DELETE in a single table

I have a database with only one table as below:

userurltag(id,userID(string),Url(String),tag(String))

I want to delete users that have less than 3 urls associated with them. How can I do that?

Upvotes: 1

Views: 190

Answers (2)

newtover
newtover

Reputation: 32094

Try this one:

DELETE
    FROM userurltag USING userurltag
    JOIN
        (SELECT userID
         FROM userurltag
         GROUP BY userID HAVING COUNT(*) < 3) as tmp
ON userurltag.userID = tmp.userID;

Upvotes: 2

Leslie
Leslie

Reputation: 3644

DELETE 
FROM userurltag 
WHERE UserID IN 
(SELECT UserID FROM userurltag GROUP BY userID Having COUNT(UserID) < 3)

Upvotes: 0

Related Questions