if 3 duplicate records in table,i want to delete 2 alternating duplicate recor

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?

Question Posted / jagan

2 Answers
3883 Views
I also Faced
E-Mail Answers

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