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 |
What is model database in sql server?
How to round a numeric value to a specific precision?
How many types of objects are there?
List all the types of user-defined functions?
what is the different types of backups available in sql server? : Sql server database administration
Mention the differences between local and global temporary tables.
What is the difference between Userdefined function and stored procedure? Explain and give the example also
Why should one not prefix user stored procedures with ‘sp_’?
Can we linked SharePoint to a SQL database?
0 Answers Sans Pareil IT Services,
What do we have to check in database testing?
What are views in ms sql server?
What options are available to audit login activity? : sql server security