[thelist] SQL: Deleting every row over 30

[Howard Cheng]

This would depend on your database server. I'm guessing that because you're
using a LIMIT clause, you have MySQL. If you've got MySQL 4 then you could
do it as a sub-select.

DELETE FROM as_discussion
WHERE parent=7
   AND id NOT IN (
         SELECT id
         FROM as_discussion
         WHERE parent=7
         ORDER BY dtime DESC
         LIMIT 0,30
)

Otherwise, it will be a multi-step process, which is pretty much the same
as the query above, but you'll have to split it into multiple queries in
your scripting language. Select the 30th date (SELECT id FROM as_dicussion
WHERE parent=7 ORDER BY dtime DESC LIMIT 29,1), then delete all messages
older than that.

At 11:24 PM 3/11/2002 +0200, Lauri Vain wrote:

>We're doing a classified system and for each item category we have, what
>the project manager calls "discussion pages". It basically operates in a
>simple 'guestbook style' with the exception that only 30 newest posts
>will be kept in the database (for each category). Older posts will be
>deleted altogether.
>
>Obviously, I can't do
>"DELETE FROM as_discussion WHERE parent='7' LIMIT 30,5" (You can't use
>two numbers in LIMIT in case of a DELETE)
>
>So, what would be the best way to delete every row over 30 from the
>database? Any good suggestions?

::::::::::::::::::::::
Howard Cheng
howcheng at ix.netcom.com
AIM: bennyphoebe
ICQ: 47319315