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
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 is sql server replication? : sql server replication
What is sql server profiler trace data file?
What do you understand by user-defined function in the sql server?
What is the recovery model? List the types of recovery model available in sql server?
How fixed length strings are truncated and padded?
What are extended events in sql server?
Do you know what is a trace frag? Where do we use it?
Does the unique constraint create an index?
What is model database in sql server?
How to update multiple rows with one update statement in ms sql server?
Your table has a large character field there are queries that use this field in their search clause what should you do?
What is function of ROLLUP ?
what are the critical issues you have resloved in your company
what is the sql equivaent of the dataset relation object ?
What are the 10 characteristics of data quality?