Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 3796 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
How check triggers in sql server?
What happens when the SQL Azure database reaches Max Size?
What are the security related catalog views? : sql server security
How to view the error log for any specific instance? : sql server database administration
explain databases and sql server databases architecture? : Sql server database administration
What is a view in sql?
What is cdc in sql server?
Do you know what are acid properties of transaction?
What is the difference between for trigger and after trigger?
How to send email from database?
How do I find the sql server version?
How do you implement session management in SQL Server mode?
what is datawarehouse?
How to define output parameters in stored procedures?
How can you tell if a database object is invalid?
