[thelist] SQL: Deleting every row over 30

Warden, Matt mwarden at mattwarden.com
Mon Mar 11 15:40:08 CST 2002


On Mar 11, Lauri Vain had something to say about [thelist] SQL: Deleting...

>So, what would be the best way to delete every row over 30 from the
>database? Any good suggestions?
>
>Thanks in advance!
>
>
>For those interested, the DB layout:
>--------- DUMP ---------
>CREATE TABLE as_discussion (
>  id INT(11) NOT NULL auto_increment,	/* Unique ID */
>  parent INT(11),					/* Parent
>category */
>  name text,					/* Post author */
>  email text,					/* E-mail of poster */
>  title varchar(200) default NULL,		/* Post title */
>  body text,					/* Body text */
>  dtime datetime default NULL,		/* Time */
>  PRIMARY KEY (id),
>  UNIQUE KEY id(id)
>) TYPE=MyISAM;
>--------- DUMP ---------

note: this is untested...

if your database supports subqueries (i.e. it's not MySQL 4.0 or
lower... heh):

DELETE FROM as_discussion WHERE id NOT IN (SELECT id FROM as_discussion
ORDER BY dtime DESC LIMIT 30)


--
mattwarden
mattwarden.com




More information about the thelist mailing list