How to automatically limit the number of rows in a database?

Question

I have the following table

+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| ID        | int(11) unsigned | NO   | PRI | NULL    | auto_increment | 
| userid    | int(10) unsigned | NO   | MUL | NULL    |                | 
| logintime | int(10) unsigned | NO   |     | NULL    |                | 
| loginIP   | int(4) unsigned  | NO   |     | NULL    |                | 
+-----------+------------------+------+-----+---------+----------------+

I insert a new row in this table every time a user logs in, which contains their login time, IP address etc...

Now I want to limit the number of rows to only 10 per user! i.e. if there's 10 rows for userid 5, already and I'm inserting a new one, I need to delete the oldest one of them before the insert.

is there a way to write a SQL statement that DELETEs all entries of a userid, except for the recent 10 ones. I know how to use LIMIT for a SELECT, but I cannot see how I can implement the LIMIT in a DELETE.

Seems like a perfect case for using a [trigger](http://dev.mysql.com/doc/refman/5.0/en/triggers.html) — bfavaretto, Feb 08 '12 at 17:34
@Riki Lyng SQL does not provides this feature directly. As bfavaretto said, you may add a trigger that deletes or restricts insertion to your table. Other way, is to modify your code that is calling the MySQL database to limit insertions — umlcat, Oct 24 '14 at 19:05

score 4 · Answer 1 · edited Feb 08 '12 at 17:58

4

Something along these lines should work:

DELETE FROM
  table 
WHERE
 userID = xyz and id not in (
  SELECT id FROM table WHERE userID = xyz ORDER BY logintime DESC LIMIT 10
 )

-- added: where userID = xyz

edited Feb 08 '12 at 17:58

Mchl

61,444
9
118
120

answered Feb 08 '12 at 17:32

D. Lambert

1,304
6
12

Sorry guys, this doesn't work with MySql. I get the following error: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – Riki Lyng Feb 10 '12 at 18:20
Following shows a slightly different subquery syntax, but it's for a select - maybe it'll work for a delete: http://stackoverflow.com/questions/2856397/mysql-subquery-limit – D. Lambert Feb 10 '12 at 18:37
Since you want the rows *not* in that subquery, you'd have to join on ID <> subquery.ID – D. Lambert Feb 10 '12 at 18:38

score 0 · Answer 2 · answered Feb 08 '12 at 17:45

0

DELETE from table
WHERE id NOT IN(
      SELECT id FROM table GROUP BY userid ORDER BY logintime DESC LIMIT 10)

answered Feb 08 '12 at 17:45

Skyrel

200
3
12

How to automatically limit the number of rows in a database?

2 Answers2