Post reply

Returning a RID

  • April 23, 2004 at 8:49 am

    #161463

    I have a table that does not have an index.  

    There are some duplicate rows in the table, that I would like to get rid of, but if I issue a delete, it will delete both rows. 

    I was weondering if in a select query, could I return the SQL Server RID, and use the unique RID in my delete statement????

    Thanks in Advance

    Eric

  • April 23, 2004 at 9:31 am

    #504029

     

    This article should help in deleting duplicates from your table:

    http://www.sql-server-performance.com/rd_delete_duplicates.asp

  • April 23, 2004 at 9:33 am

    #504030

    I assume you mean no unique identifier and hence cannot differentiate between rows?

    What about extracting the data to a temp table, eliminating duplicates in the SELECT process (i.e. SELECT DISTINCT), deleting all records and inserting back?

  • April 23, 2004 at 9:35 am

    #504031

    You can use the rowcount option

    SET ROWCOUNT {number | @number_var}

     

    Set it to 1 and issue the delete statement it will delete only one row(incase you have two duplicates).

    Like that if you have 5 duplicates then you can set it to 4 and issue a delete it will delete 4 and leave one behind.

    You can write a small stored proce to get it dynamically how many duplicates are there based on fields you are looking for duplicates and set rowcount and issue delete.    I do not think there is any way to access the RID, I believe its used internally.

    HTH

    Thanks,

    Murthy

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply