if 3 duplicate records in table,i want to delete 2 alternating
duplicate records by keeping 1 duplicate and 1 original as it
is,how?
- 2 Answers
- 2969 Views
- I also Faced
- E-Mail Answers
Answer Posted / mohamed ibrahim
Deleting multiple duplicate rows in a table
Ex . I have the Table named as TestMaster
to delete duplicate rows from the testmaster using Cursor &
RANK() Function.
for ex.the table having the fields ID,Name
the having the following data
oupput:
ID NAME
1 Raja
1 Raja
1 Raja
2 Mohamed
2 Mohamed
2 Mohamed
To Delete duplicate Rows in table to follow the below code:
DECLARE @ID INT
DECLARE delduplicaterecords_Cursor CURSOR
FOR SELECT ID FROM TempMaster
OPEN delduplicaterecords_Cursor
FETCH NEXT FROM delduplicaterecords_Cursor INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
WITH CTE
AS
(SELECT
ROW_NUMBER () OVER (ORDER BY ID) AS RowID,
*
FROM TempMaster WHERE ID=@ID )
DELETE FROM CTE WHERE RowID <> 1
FETCH NEXT FROM delduplicaterecords_Cursor INTO @ID
END
CLOSE delduplicaterecords_Cursor
DEALLOCATE delduplicaterecords_Cursor
| Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
What are logical database components? : SQL Server Architecture
What is scheduled job and how to create it?
How to create function with parameter in sql server?
What are the basic functions for master, msdb, model, tempdb databases?
What is failover clustering overview?
but what if you have to create a database with two filegroups, one on drive c and the other on drive d with log on drive e with an initial size of 600 mb and with a growth factor of 15%? : Sql server database administration
What are partitioned views and distributed partitioned views?
What is service broker? : sql server database administration
How to apply filtering criteria at group level with the having clause in ms sql server?
What is use of @@ spid in sql server?
What are a database and a data warehouse?
How to modify the underlying query of an existing view?
explain what is a schema in sql server 2005? Explain how to create a new schema in a database? : Sql server database administration
Can select statements be used on views in ms sql server?
What the different topologies in which replication can be configured?
