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
- 3134 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
Difference between report and query parameter. Why do we need different type of parameter?
Explain what is the main purpose of having conversation group?
What are the properties of the relational tables?
What is the difference between createstatement and preparedstatement?
How to list all schemas in a database?
What is update locks?
How to copy data from one table to another table?
Explain the cursor lock types?
What do you understand by intent locks?
What are actions, how many types of actions are there, explain with example? : sql server analysis services, ssas
How do you make a trace?
What is nolock?
What is the purpose of forms?
I have a table Events Events containing cardno,time,id,name--each id has a cardno my requirement is every day each employee swipe the card several times i want to calculate first and last time of each card the output should be name 1 2 6 7 in out in out holiday holiday xxx 09:30 06:30 09:40 06:45 where 1,2...... are dates for example january 1,2, etc. 6 and 7 are saturday and sunday how it is posssible
What is a transact-sql statement?
