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?
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / sivam
sno sname salary
1 aaa 1000
1 aaa 1000
2 bbb 2000
2 bbb 2000
2 bbb 2000
1 aaa 1000
;with aa as
(
select sname,salary,ROW_NUMBER()over(partition by sno,sname,salary order by sno,sname,salary) as Nos from #testtable
)
delete from aa where Nos%2<>0
Is This Answer Correct ? | 0 Yes | 0 No |
How to get the definition of a stored procedure back?
What do you understand by a view? What does the with check option clause for a view do?
What is deadlock and how to avoid the deadlocks.
How to add a new column to an existing table with "alter table ... Add" in ms sql server?
How to update muliple row in single query?
What are primary keys and foreign keys?
How can we call UDF(User Define Function) using C# code in ASP.net ?
what are different types of raid configurations? : Sql server database administration
Suggest a method of joining two tables.
0 Answers Ernst Young, Thomson Reuters,
Explain the different types of backups available in sql server? : sql server database administration
Which database stores information about replication?
how many triggers you can have on a table? : Sql server database administration