i want to delete all private messages a user recived but not the latest five.

*turn-on-creativity-mode*

DELETE FROM `xoops_priv_msgs` WHERE to_userid=1 ORDER BY msg_id ASC LIMIT COUNT(*) - 5

*turn-off-creativity-mode*

but it's not working *g*. so is there a possibility to do it within one statement?

any ideas?

I don't think it's possible to do that with a single query, except maybe with the subquery feature available in MySQL 4.1+.

If you're running MySQL < 4.1, you could do one query to determine the maximum msg_id to delete, and then do a second query to perform the deletion. Or you could write a PHP script to do it.

I'm also not sure whether it's safe to remove private messages simply by deleting them from the table. It probably is, but I'd check the existing script that deletes PMs to make sure there isn't any other cleanup involved.