Delete duplicate rows from a table without primary key by
using a single query
Table Employee
empname salary
A 200
B 300
A 200
C 400
D 500
D 500
Output should be
A 200
B 300
C 400
D 500
- 14 Answers
- 20106 Views
- I also Faced
- E-Mail Answers
Answer Posted / swati tripathi
declare myCursor cursor for
select empid
from employee
group by empid
having count(*) > 1
declare @EmpId int
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @EmpId
WHILE @@FETCH_STATUS = 0
BEGIN
delete top(select count(*)-1 from employee where
empid=@EmpId) from employee where empid=@EmpId
FETCH NEXT FROM myCursor INTO @EmpId
END
close myCursor
deallocate myCursor
| Is This Answer Correct ? | 11 Yes | 0 No |
Post New Answer View All Answers
What are statistics?
Explain what is lock escalation and what is its purpose?
what are different types of backups available in sql server? Given a particular scenario, how would you go about choosing a backup plan? : Sql server database administration
What is the difference between join and inner join?
What is create statement?
Explain the functionalities that views support?
Explain the working of sql privileges?
what purpose does the model database serve? : Sql server database administration
When does the auto update index statistics feature in sql server turn itself on?q) what specific conditions database should meet, before you can bulk copy data into it using bcp?
Why the trigger fires multiple times in single login?
How to disable stored procedure sql server?
What is raid, and how it can influence database performance?
Is the primary key column of a table an index in ms sql server?
How to disable a login name in ms sql server?
How to create a simple stored procedure in ms sql server?
