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



if 3 duplicate records in table,i want to delete 2 alternating duplicate records by keeping 1 dupl..

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

if 3 duplicate records in table,i want to delete 2 alternating duplicate records by keeping 1 dupl..

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

Post New Answer

More SQL Server Interview Questions

What is model database in sql server?

0 Answers  


How to round a numeric value to a specific precision?

0 Answers  


How many types of objects are there?

0 Answers  


List all the types of user-defined functions?

0 Answers  


what is the different types of backups available in sql server? : Sql server database administration

0 Answers  






Mention the differences between local and global temporary tables.

0 Answers  


What is the difference between Userdefined function and stored procedure? Explain and give the example also

5 Answers  


Why should one not prefix user stored procedures with ‘sp_’?

0 Answers  


Can we linked SharePoint to a SQL database?

0 Answers   Sans Pareil IT Services,


What do we have to check in database testing?

0 Answers  


What are views in ms sql server?

0 Answers  


What options are available to audit login activity? : sql server security

0 Answers  


Categories