Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Deleting Duplicated Rows

    Hi,
    I have a table named "std_attn", where, by some bad coding, lots of duplicated rows have been created. And the table don't have any PK. So Now tell me the way to remove the duplicaies..................


    thnx

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DROP DATABASE database_name





    just kidding

    do you want to remove the duplicates, or remove all but one duplicate?

    and if all but one, how do you pick which one?

    is there some column that is incrementing, like an IDENTITY or a dateadded column or something? if so, you could keep the last one of each group...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Since there is no PK, insert the duplicate records into a temp table with an identity column. You can then delete the records from the source table and insert a SELECT DISTINCT from the temp table back into your source table. After this is done, put a constraint on the table and fix the application.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    derrick, how does this isolate which one of the duplicates to keep?

    personally, i like the approach offered in this article:
    Advanced SQL Techniques - Part 1: Analyzing Duplicate Records
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    ??? They're duplicates. Who cares which one you keep. This gets rid of all of them, then inserts one back in.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Hi,
    No Derrick, its not that I don't want any of the duplicate rows. I need one. I will tell you, what actually i was trying.............


    1. I made a temporary table which was the copy of the original one
    2. Then copied all the rows from original to the temporary one.
    3. Then deleted all rows from the original one
    4. Defind the PK in the original table
    5. Added a column in temporary table with allow null property. The data type was boolean. It was made for the reason to update the it with the value "yes" if the rows was having any duplicate row and "no" in the row is unique.
    6. Then insert into the original from temporary where the column value is equal to "no"


    But unfortunetly I failed at the 5th step only. I could not get the correct SQL stetement....

    So it'll be a great help, if any one can get me the correct SQL statement


    thnx

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try the method in the article i gave you the link for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Ok guys, this isn't that hard. The article you referred to is in reference to tables that have an identity column, which means there's something in the row that makes it intrinsically unique even though it's just an ID field. This works great, except for the fact his table does not have an identity column. This means the idea in the article does not work.

    That's why he needs to do what I told him to do, which does work. There's really not a "better" way to do it if there's nothing to distinguish between the rows in the table. You were doing fine until you tried to get fancy.

    1. Insert the duplicate records into a temp table with an identity column.
    2. Delete the duplicate records only from the source table.
    3. Insert a SELECT DISTINCT from the temp table back into your source table.
    >>This part is really important and you kind of missed it last time around.
    4. Put a constraint on the table and fix the application.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by derrickleggett
    ??? They're duplicates. Who cares which one you keep. This gets rid of all of them, then inserts one back in.

    Wanna bet....


    U$1000.00 That they're not.......betcha something is different
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Then he needs to ask the right question. I'm answering the one he asked, so I really don't care if something is different. How ya' doing Brett. I just keep seeing you on these forums. lol
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But just like the day to day job you're in...is the question asked EVER what the real question is, and what the real answer is suppose to be?

    Ever?

    OK, sometimes...but rarley.....

    And yeah....an addiction is an addiction...sorry....I just can't help myself....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If he did ask the question appropriately though, the answer is correct. If not, then the article suggested should work. We both know the real issue is the fact he got the rows in the first place. The reality is that the application needs fixed and constraints need to be created so it doesn't happen in the first place.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by DBA_Rahul
    Hi,
    where, by some bad coding, lots of duplicated rows have been created. And the table don't have any PK.
    Yeah Derrick...I'm not disagreeing with you ...the answers solve the the question asked...

    I just don't believe the question...

    Some bad coding....puuuuuuleeeeeeeeeeze...

    Some bad db design....

    A good db design FORCES the developers to do the right thing....

    @@ERROR <> 0

    and to be TOTALLY politically incorrect....

    EDIT: ...nope....uncalled for....sorry

    Damn...it's Monday again....

    Sorry for the foul mood....
    Last edited by Brett Kaiser; 05-24-04 at 10:12.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by derrickleggett
    You were doing fine until you tried to get fancy.
    me? fancy? that'll be the day



    1. Insert the duplicate records into a temp table with an identity column.
    why does the temp table need an identity column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Literacy is a wonderful thing. You don't. I'm smoking crack and up too late. You do need the rest though.

    It's just that Monday thing. (We'll all use that as an excuse today.)
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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