SQL
I have some duplicate rows in my SQL database table, Please Tell me how'll delete duplicate
Software/Hardware used:
Software/Hardware used:
ASKED:
August 14, 2008 8:58 AM
UPDATED:
August 18, 2008 3:21 PM
Answer Wiki:
Without a primary key there's no fast easy way to do this. You'll need to handle each one, one at a time.
If you are using SQL 2000 or below you'll need to do something like this.
<pre>SET ROWCOUNT 1
DELETE FROM YourTable
WHERE Col1 = 'Something'
AND COl2 = 'SomethingElse'
/*Add what ever filters are needed to isolate the duplicate row*/</pre>
If you are using SQL 2005 or above you'll ned to do something like this.
<pre>
DELETE TOP (1) FROM YourTable
WHERE Col1 = 'Something'
AND COl2 = 'SomethingElse'
/*Add what ever filters are needed to isolate the duplicate row*/</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
I ran into this issue recently and solved it using a select distinct statement against all the columns to make a new table with unique rows as a materialized query. In my case I was then able to confirm the contents of the new table, delete the original and rename the new table. It would be more complicated if there are dependencies on the table, though.
SELECT DISTINCT (column1, column 2, column 3, column 4,…, column n)
FROM Table
I’d also recommend adding a primary key.