how to delete duplicate rows in sql server2005
- 7 Answers
- 11341 Views
- Cisco, CTS, HCL, IBM, I also Faced
- E-Mail Answers
Answer Posted / arunkumar_mlx
WITH A
AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY
columnname_1,columnname_2 ORDER BY columnname_1) AS
duplicate FROM table_name
)
SELECT * FROM A WHERE duplicate>1
--DELETE FROM A WHERE duplicate>1
First select and you can find the row_number having more
than 1 rows.
Then delete them on comment of select stmt inside the
query..
decomment of that delete stmt will delete the duplicate
rows.
| Is This Answer Correct ? | 8 Yes | 4 No |
Post New Answer View All Answers
Other than truncate statement, which other command can by-pass the trigger on the tables?
What are the steps to follow to configure SQL*Net?
Can we update data in a view?
What are sparse columns?
You want to implement the many-to-many relationship while designing tables. How would you do it?
What is temporary stored procedure?
What is the difference between commit and rollback?
What is a rollup clause?
How to create a trigger for insert only?
How to get a list of columns in a view using the "sp_help" stored procedure?
What is the sql profiler?
What is cdc in sql server?
What causes index fragmentation?
What is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure? How many levels of sp nesting are possible?
when you create a database how is it stored? : Sql server database administration
