SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete Mutiple entries that are the same (MS SQL)

    Hi all,

    If I have a table that contains multiple data that is the same across all columns apart from the primary key, how can I delete all these multiple entries but just leave one entry in the table for each set of identical multiple entries?

    Thanks

    Tryst

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    DELETE FROM yourtable
    WHERE somecolumn=somevalue
    AND someothercolumn=someothervalue
    AND primarykey <> value_of_row_you_want_to_keep
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but is there an automated way of getting rid of many occurances where the columns are the same, for example there could be 20 rows where the columns are the same, and then followed by another set of columns where the values are different from the last 20 columns but the values are the same for the next 30 columns etc etc.

    Or do I have to do it manually through a query like the one you have given?

    Thanks

    Tryst

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, you do not have to do it manually, but you have given us blessed little to go on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, this is what I meant

    Code:
    id - col1 - col2 
    1  - xxxx - xxxx
    2  - xxxx - xxxx
    3  - xxxx - xxxx
    4  - yyyy - yyyy
    5  - yyyy - yyyy
    6  - yyyy - yyyy
    7  - mmm - mmm
    8  - mmm - mmm
    9  - mmm - mmm
    So is there a way that all those duplicate rows can be deleted in one query? Or do I have to run a query for each batch of rows that are identical?

    Thanks

    Tryst

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    all those duplicate rows, or all but one? and if all but one, how do you determine which one to keep?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its ok, r937, its sorted now.

    I had to run a query that had the DISTINCT keyword in it to prevent the data be populated teh way I had described.

    Thanks

    Tryst


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
  •