duplicate rows

Results 1 to 3 of 3

Thread: duplicate rows

  1. #1
    Join Date
    Dec 1969

    Default duplicate rows

    Hi<BR><BR>I am worling on sql server 2000.<BR>I have 2 ques<BR>1. I want to delete the duplicate rows from my table<BR>key is suppid and empid means want to delete duplicate records of same suppid and empid.<BR><BR>2.I have one table with 10 fields.I dont have any primary key in this table.when i perform the delete operation (just delete from table no more where conditions) its taking long time to delete.<BR>is there any way to speed up it can i create index on this table if can to which field i have to create because i dont have any where condition in my sql query.<BR><BR>Pl help me thanks<BR>

  2. #2
    Join Date
    Dec 1969

    Default If you don't have any indexes...

    ...on your table, than NOT IN or such like is going to veeery slow. What might be faster is to sort the table by all the fields and then loop through the recordset you get. All duplicates are no banked together, so all you need to do is compare the current record with the previous (or next) record and if they&#039;re the same, delete one of them.<BR><BR>For example:<BR><BR>MyTable_with_Duplicates:<BR><BR>1, Oliver, $10000<BR>1, Mark, $2000<BR>2, Oliver, $10000<BR>1, Mark, $3000<BR>2, Oliver, $10000<BR>1, Bob, $5000<BR>1, Mark, $3000<BR><BR>Now, if you order everything by all the fields, you get:<BR><BR>1, Bob, $5000<BR>1, Mark, $2000<BR>1, Mark, $3000<BR>1, Mark, $3000<BR>1, Oliver, $10000<BR>2, Oliver, $10000<BR>2, Oliver, $10000<BR><BR>As you can easily see, the two duplicates (1, Mark, $3000 and 2, Oliver, $10000) are now right next to each other. Just loop through this list, as I described above and, hey presto, it&#039;s deudped.<BR><BR>Oliver.

  3. #3
    Join Date
    Dec 1969

    Default For question 2

    If you&#039;re simply saying you want to delete ALL the records from table then use &#039;TRUNCATE&#039; instead of &#039;DELETE&#039;. <BR><BR> truncate table yourtable

Posting Permissions

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