How to retrieve duplicate rows in a table?
How to delete the duplicate entries in a table?
Answer Posted / skybeaver
/* selecting duplicate rows in a table */
select col1, col2, ..., colN, count(*)
from TableName
group by col1, col2, ..., colN
having count(*) > 1
/* deleting duplicate rows from a table */
select col1, col2, ..., colN, count(*) as "Duplicates"
into #duplicates
from TableName
group by col1, col2, ..., colN
having count(*) > 1
delete TableName
from TableName t, #duplicates d
where t.col1 = d.col1 and
....
t.colN = d.colN
/* damn I'm good! */
Is This Answer Correct ? | 11 Yes | 5 No |
Post New Answer View All Answers
Can two tables share a primary key?
How to find the source of a table in sql server?
What is a View ? Can we insert, Update and delete a view?
what is package and it uses and how can u call a package
What is encryption key?
Explain the functionalities that views support?
explain different types of constraints? : Sql server database administration
How to create hyperlink from returned sql query ?
What is the server name in sql server?
Can a cursor be updated? If yes, how you can protect which columns are updated?
How to get nth highest salary from employee table.
why does a sql statement work correctly outside of a user-defined function, but incorrectly inside it? : Sql server administration
How will you find out if there are expensive SQL statements running or not?
What are the acid properties?
What happens if ntwdblib.dll is missing on your machine?