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
- 3787 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
What stored by the master? : sql server database administration
How you can get a list of all the table constraints in a database? : Sql server administration
Define a cross join?
Why olap is used?
What samples and sample databases are provided by microsoft?
What is compression - row-level and page-level compression?
What are the properties of the transactions?
Explain the use of containers in ssis?
How to get a list all databases on the sql server?
Tell me about the approaches which you used to counter the DI problems.
What are the 3 types of schema?
What is row-level compre?
Why should we go for stored procedures? Why not direct queries?
What authentication modes does sql server support?
What is change tracking in sql server?
