SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: SQL Query

  1. #1
    Free me php klassicd's Avatar
    Join Date
    Sep 2001
    Location
    San Diego
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query

    Is there a way to delete all the rows after the 10 newest rows with a sql query. (mysql)

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe...
    DELETE FROM table
    WHERE id < (MAX(id) - 10)

    (If id column is auto-incremented)

  3. #3
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but that assumes no rows within the newset 10 have been deleted already
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  4. #4
    Free me php klassicd's Avatar
    Join Date
    Sep 2001
    Location
    San Diego
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cant

    I cant do it that way because he is right. SOme of the rows before or between have been deleted. Is there another way?

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Correct point from crowdozer

    Does this work?
    DELETE FROM table
    WHERE id < (SELECT id FROM table ORDER BY id DESC LIMIT 10,1)

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I assume by 'after 10 newest rows' you mean delete anything older than the 10 newest rows?

    Do limits work on deletes?

    If so,
    Code:
    LOCK TABLE table
    
    // mixing PHP and SQL here ;)
    SELECT $count = COUNT( * )
      FROM table
    
    If $count > 10
    // Ok we know there are say 120 rows in the table.
    // Delete the first 110 rows
    DELETE TABLE
     ORDER BY id ASC
     LIMIT $count - 10

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But more to the point -- why are you doing this? Maybe we can help engineer a better solution.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •