Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question how to delete duplications permanantly from table using sql server7.0

    hi all,

    i have one table. at the time of transactions some duplications are inserting in the database.
    i want to delete duplications but not distinct rows. Means i want only distnict rows in the database.
    how it is possible using sql server7.0.

    i have one idea. At first i want to create a Alias of particular table. i will shift rows to the alias table. after that i will delete origional.
    is it works???

    if you find answer about this please.... reply.

    thank you advancedly
    shree

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    - copy the structure of your table (structur, without data. or if you don't know how to do this, copy table and delete all records)
    - create a unique index (thats an index that doesn't allow duplicate values) on the field(s) that should be unique (combinations)
    - run an appendquery to insert all records from your original table into this new table --> the duplicate records will not be inserted. Only the first of the duplicate records will be inserted.
    - delete your original table
    - rename your new table to the original tables name
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New Coder
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to see duplications

    hi

    thank you once again.

    and i have one more doubt. please..

    i want to see the duplication records only. How to write a query to this problem using sqlserver 7.0

    thank you. iam really appreciating you because of your helping.

    sincerely
    rajashree

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you can do that by running a count on the (combination of) column(s) that should be unique --> the ones that you would create your unique index on.

    like
    SELECT COUNT(*), column1, column2 FROM yourtable GROUP BY column1, column2

    all records with a count higher then 1 are duplicates. You can create a temporarely table of this, or use it as a subquery to get all info from the duplicated records out of the original table.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

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