Jump to content

Delete oldest information, to keep database small


IsmAvatar

Recommended Posts

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.

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.