[thelist] SQL: Deleting every row over 30

[rudy]

> 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.

you can also do it without manipulating any values in the scripting,
using this series of queries --

  create temptable
      select * from as_discussion
          where parent=7
             order by dtime desc
           limit 30

  delete from as_discussion

  insert into as_discussion
     select * from temptable

  drop temptable


not tested, but you get the idea

aside:  note another example of select star being okay, in fact preferred

efficiency of creating/dropping a temp table might be questionable, but
this series of queries won't be run often, and presumably only by an admin

there may be unpredictable results during the time these queries are
running

cries out for a transaction block, don't it?

but then, if you had a transaction block, you'd also have the subquery...


rudy