Removing duplicate records

[aharrisreid]

I have a table containing hundreds of duplicate records (even the primary key is duplicated!) which I wish to remove. The first thing that came to mind was SELECT DISTINCT * FROM MyTable, but MyTable contains a text field, hence I get the error "The text, ntext or image data type cannot be selected as DISTINCT".

Is there any 'neat' way of ending-up with distinct records with text fields?

TIA

Alan

 

[donutman]

Are you using Access? I don't think you can get SQL Server to ever accept a duplicate PK as Access just turns off the constraint.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[aharrisreid]

Hi donutman, thanks for the reply.

>I don't think you can get SQL Server to ever accept a >duplicate PK as Access just turns off the constraint.

I am using SQL server, and under 'normal' circumstances you would be correct - if the table was setup correctly a duplicated PK would not be possible.

This table was the result of a SELECT statement that had incorrect table joins, and as a result produced duplicate records.

Regards,
Alan

 

[donutman]

Here's the quick and dirty approach that I'd use:
Create another table for the image data (or whatever) and populate it with just a

[Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] PK [Blue]FROM[/Blue] YourTable

Then update that table adding the image data by doing an inner join between the two tables. The update will only be done once for the duplicate rows.
Now you should copy the existing design of the table and create a new table to hold the final results. Then alter the original table by deleting the image data column(s). Finally populate the new permanent table with a Select Distinct as you originally intended. Then update it with the image data.
Your back in business!
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[aharrisreid]

Hi donutman, thanks for the reply.

>Create another table for the image data (or whatever) and >populate it with just a

>CODE
>SELECT DISTINCT PK FROM YourTable
> Then update that table adding the image data by doing >an inner join between the two tables. The update will >only be done once for the duplicate rows.
> Now you should copy the existing design of the table >and create a new table to hold the final results. Then >alter the original table by deleting the image data column
>(s). Finally populate the new permanent table with a >Select Distinct as you originally intended. Then update >it with the image data.

Mmmmm...not as easy as I thought it might be. I was hoping I might be able to achieve my goal via. SQL commands alone, but it looks like I might have to resort to something like you suggest above.

Regards,
Alan

 

[donutman]

Yes, it would be nice if it could be done by a single Delete command, but it can't. Here's why. There is no way to single out one of two identical rows for a delete. Sure you can order rows, but the delete command doesn't work on the basis of an order. A Delete statement either deletes all of the records in a table or you have to tell it to delete based upon matching some condition of the fields in the row. Since the rows are identical, you have no choice but to delete all of the matching rows. That is why you must do this project by populating other tables. They can be (in memory) temporary tables, but it's easier to work through the solution with real tables. The temporary table approach would be better if you were planning to do this more than once.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]