Delete All Threads Except the Last 500

       
mysql>
mysql> CREATE TABLE messages (
    ->   msgID int(20) NOT NULL auto_increment PRIMARY KEY,
    ->   forumID int(11),
    ->   rootID int(11),
    ->   parentID int(11),
    ->   userID int(11),
    ->   subject varchar(80),
    ->   msgText text,
    ->   ts timestamp
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO messages VALUES (60,1002,60,NULL,375,'cgi','','2001-03-27 08:35:28'),
    ->                             (62,3,62,NULL,162,'message','','2001-03-29 16:07:29');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql>
mysql> SELECT @oldtime := ts FROM messages
    -> WHERE rootID=msgID ORDER BY ts DESC LIMIT 499,1;
Empty set (0.00 sec)

mysql>
mysql> drop table messages;
Query OK, 0 rows affected (0.00 sec)

mysql>

Related examples in the same category

1.	Deleting Rows with DELETE
2.	Using DELETE LOW_PRIORITY command
3.	Delete by JOIN
4.	Delete with JOIN 2
5.	Using the 'from join delete' Alternative to Delete Data
6.	Delete row with condition
7.	Delete statement with subquery (ERROR 1093 (HY000): You can't specify target table 'EmployeeS' for update in FROM clause)
8.	DELETE statement using the alternative.
9.	Joining Tables in a DELETE Statement
10.	Delete with where clause
11.	Delete ignore
12.	Adding Subqueries to Your DELETE Statements
13.	Delete rows from multiple tables

Delete All Threads Except the Last 500 : Delete « Insert Delete Update « SQL / MySQL

Related examples in the same category