CODEWITHSUNDEEP
mysql
Jordash
Jordash

Reputation: 3093

MySQL Delete from database where count of grouped records less than value

I have a database similar to this:

Name    State
Bill    CA
Joe     NY
Susan   CA

I know I can get a total count of the number of records for each state like this:

SELECT State, COUNT(*) as count FROM users GROUP BY State

I'm trying to delete all records where the total count of states is less than 2 (or any arbitrary number)

Something like this (Pseudocode):

 DELETE FROM users WHERE totalUsersInState < 2

So the final database should be like this

Name    State
Bill    CA
Susan   CA

What is the correct syntax for that? I can't figure it out.

Upvotes: 0

Views: 44

Answers (2)

spencer7593
spencer7593

Reputation: 108400

We can use a join to an inline view (a derived table in the MySQL parlance)

Write it as a SELECT statement first

SELECT t.*
  FROM users t
  JOIN ( SELECT r.state
           FROM users r
          GROUP 
             BY r.state
         HAVING SUM(1) < 2
       ) s
    ON s.state = t.state

Verify that these are the rows we want to delete, and then convert that into a DELETE statement by just replacing the first SELECT keyword...

DELETE t.*
  FROM ...

Note that this will not remove a row with a NULL value for state because of the equality comparison in the join predicate.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86715

One option, fins all states with fewer than less that 2 users, then delete all records for those states.

DELETE FROM
  users
WHERE
  state IN (SELECT state FROM users GROUP BY state HAVING COUNT(*) < 2)

Or (because the < 2 means "delete users where they are the only user in the state")...

DELETE FROM
  users
WHERE
  NOT EXISTS (SELECT *
                FROM users lookup
               WHERE lookup.Name <> users.Name
                 AND lookup.State = users.State
             )
             -- WHERE NOT EXISTS (any _other_ user that's in the same state)
             -- => WHERE this is the only user in the state

Upvotes: 0

Related Questions