SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Deleting rows

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Deleting rows

    Hello.

    Got something like this:
    Code:
    Date   | Value
    ================
    today  | 9000
    --------------
    today  | 8000
    --------------
    today  | 7000
    --------------
    today  | 6000
    --------------
    today  | 5000
    --------------
    today  | 4000
    --------------
    today  | 3000
    --------------
    today  | 2000
    --------------
    I want to remove all the rows omitting 5 top rows.

    We can select rows ommiting 5 top by:

    Code SQL:
    SELECT * FROM stats
    ORDER BY VALUE
    LIMIT 5,10

    but cannot achieve , delete action.

    Any proposals will be appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    does your table have a primary key?

    also, how often do you want to do this? once? or repetitively, and if so, why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I've got a primary key "ID" ( auto increment - starts from 1 ). I am filling table with data and then I want to get rid of records that dont fit in first 20 records, ordering by VALUE as mentioned above.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, i understand what you want to do

    how often?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Once a day

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, then you will need a separate table to store the ids in

    (mysql will not let you delete from the same table you're selecting from, so you have to do it in two steps)

    DELETE FROM top_ids; -- clears the table

    INSERT INTO top_ids
    SELECT id FROM stats
    ORDER BY value_column LIMIT 0,20; -- stores top 20 ids in top_ids table

    DELETE FROM stats
    WHERE id NOT IN ( SELECT id FROM top_ids ) -- vwalah
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, now we've got an issue and solution, hopefully someone will use it again.


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
  •