Delete oldest information, to keep database small

[IsmAvatar]

I have a little chat area which stores the date/time and the message. This chat area will never have more than 30 messages (it only displays the 30 most recent messages), so I figured I could just delete the oldest rows from the database whenever a new row is inserted. It sounds simple enough, and I had no trouble writing a query for it:

 

DELETE FROM discuss
WHERE dtime NOT IN (
  SELECT dtime FROM discuss
  ORDER BY dtime DESC LIMIT 30
);

 

or in PHP:

$sub = 'SELECT dtime FROM discuss ORDER BY dtime DESC LIMIT 30';
$qry = 'DELETE FROM discuss WHERE dtime NOT IN (' . $sub . ')';
mysql_query($qry) or die(mysql_error());

 

Except for 1 small silly problem that occurs when I execute this query:

"This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"

 

I'm using PHP 5.2.5 and MySQL 4.1.22-standard.

I would consider upgrading out of the question. At this point, I think a workaround will suffice.

[hitman6003]

You may be able to use something like this:

 

DELETE FROM discuss
WHERE dtime < (SELECT dtime FROM discuss ORDER BY dtime DESC LIMIT 30, 1)

 

 

[IsmAvatar]

apparently

"You can't specify target table 'discuss' for update in FROM clause" (error 1093)

 

Which is greek to me. If I had to guess, I'd say it's probably yelling about not being able to compare a value to a table - even if it is only 1 column and 1 row.

[fenway]

This is being discussed in another thread right now...