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

Question

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.

Possible duplicate of [Possible to do a delete with a HAVING clause?](https://stackoverflow.com/questions/11705996/possible-to-do-a-delete-with-a-having-clause) — PM 77-1, Feb 02 '18 at 22:59

score 1 · Accepted Answer · answered Feb 03 '18 at 00:00

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.

MatBailie · Answer 2 · 2018-02-02T23:01:37.313

0

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

edited Feb 02 '18 at 23:01

answered Feb 02 '18 at 22:57

MatBailie

83,401
18
103
137

@PM77-1 That's a false optimisation. Go search the millions of other questions about that. It's not a discussion for this question. – MatBailie Feb 02 '18 at 23:02
1

It's simply cleaner. – PM 77-1 Feb 02 '18 at 23:03
2

@PM77-1 I disagree, and ***It's not a discussion for this question.*** – MatBailie Feb 02 '18 at 23:05
1

The first query won't work because of https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Barmar Feb 02 '18 at 23:33
The second statement will fail for the same reason as the first: Error 1093 You can't specify target table '%s' for update in FROM clause – spencer7593 Feb 02 '18 at 23:53

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

2 Answers2