Problems deleting duplicate rows from SQL table
Posted on 2004-04-13
I've hunted and hunted for a solution to this problem, but to no avail - I'll try to explain.
I have a table 'tbl_arc_leeds_sent' which has 18 columns, one of which is 'itemID' which has been set as IDENTITY.
Through being a complete idiot one day, I managed to TRIPLICATE 5738 rows of data in said table. I'm now stuck with trying to remove the 11,476 rows that are not required.
However, some of the column types are ntext, therefore the solution often offered of 'SELECT DISTINCT' wont work.
I'm not concerned with any other column other than 'itemID', as this is my key to knowing whether the rows are duplicated; that's enough of a check for me for this table.
I can find all of the duplicated entries; Query Analyzer shows all 17,214 rows - but as each unique entry shows up three times, each of these with the same 'itemID' three times, I'm having trouble.
Hopefully someone will be able to help me out!
Cheers in advance.