Delete singular entries

Question

If I have this table:

+------+-------+---------------+--------+-----------------+------------+-----------+----------------+------+------+--------+------------+------------+
| type | class | username      | userid | userip          | usermobile | useremail | daysleft| pin1 | pin2 | pin3 | active | schoolname | schoolsite |
+------+-------+---------------+--------+-----------------+------------+-----------+----------------+------+------+--------+------------+------------+
| B    | A     | sebbetest     |   1000 | 123.123.123.123 | none       | none      |       50| 0    | 0    | 0    | Y      | none       | none       | 
| A    | A     | stackowerflow |   5355 | 123.123.123.123 | none       | none      |       50| 0    | 0    | 0    | Y      | none       | haha       | 
| C    | A     | good          |   4223 | 123.123.123.124 | none       | none      |       50| 0    | 0    | 0    | Y      | none       | haha       | 
| A    | A     | tester        |   6353 | 123.123.123.125 | none       | none      |       50| 0    | 0    | 0    | Y      | none       | haha       | 
| B    | A     | admin         |   3453 | 123.123.123.125 | none       | none      |       50| 0    | 0    | 0    | Y      | none       | eeee       | 
| A    | A     | sebastian     |   1342 | 123.123.123.126 | none       | none      |       50| 0    | 0    | 0    | Y      | none       | eeee       | 
| C    | A     | username      |   6456 | 123.123.123.125 | none       | none      |       50| 0    | 0    | 0    | Y      | none       | woooooow   | 
+------+-------+---------------+--------+-----------------+------------+-----------+----------------+------+------+--------+------------+------------+

As you see, the user "good" with the IP "123.123.123.124" AND user "sebastian" with IP "123.123.123.126" has no "companions", no other users on the same IP.

The user "sebbetest" has a companion "stackowerflow".

The user "tester" has 2 companions: "admin" and "username".

Now I want to delete these users that lack companions. How I do? For atomic reasons and to prevent concurrent access from munging the database, I would want to delete all "non-companion" users in a single expression of SQL.

I tried with: DELETE FROM lan WHERE COUNT(userip) = 1;

got this: ERROR 1111 (HY000): Invalid use of group function

No rows are duplicates. If there is a need to check if a entry is unique regardless of IP, (type, userid) is unique.

In other words, if IP is unique in a row, delete it.

Answer 1

Use:

DELETE FROM lan
 WHERE userip IN (SELECT x.userip
                   FROM (SELECT yt.userip
                           FROM lan yt
                       GROUP BY yt.userip
                         HAVING COUNT(*) = 1) x )

You get the error because you can't use COUNT, or any other aggregate functions in the WHERE clause while outside of a subquery. Only in the HAVING clause can you reference aggregate functions in that manner.

It's possible this might work:

  DELETE FROM lan 
GROUP BY userip
  HAVING COUNT(*) = 1;

Warning

With every DELETE statement, check and double check that the statement will select what you want for deletion. If you are using InnoDB tables, wrap the DELETE in a transaction so you can use ROLLBACK if necessary.

Answer 2

I would do it this way:

DELETE l1
FROM lan l1 LEFT OUTER JOIN lan l2 
  ON l1.userip = l2.userip AND l1.username <> l2.username
WHERE l2.userid IS NULL

In other words, try to match l1 to its companion l2 using an outer join. If no match is found, then l2 will be all nulls. Where that happens, delete l1.