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
- 3657 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 to insert and update data into a table with "insert" and "update" statements?
Explain go command in sql server?
Why we need sql server?
How to execute function in stored procedure sql server?
how we use window authentication connection with sql server.?
what type of index will get created after executing the above statement? : Sql server database administration
Ms sql server index?
What are the different types of data sources in ssrs?
What are types of subqueries?
What is normalization 1nf 2nf 3nf?
Define compound operators?
what’s the difference between Covering Indexes and Clustered Indexes ? how to use clustered index small ?
What is page in sql server?
What is sql or structured query language?
What is use of attribute hierarchy optimized state? : sql server analysis services, ssas
