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
- 18457 Views
- I also Faced
- E-Mail Answers
Answer Posted / sunil
set rowcount 1 -- set row count 1
delete a from Employee a
where (select count(*) from Employee e where e.empname =
a.empname) > 1
while @@rowcount > 0
begin
delete a from Employee a
where (select count(*) from Employee e where e.empname =
a.empname) > 1
end
set rowcount 0
| Is This Answer Correct ? | 2 Yes | 0 No |
Post New Answer View All Answers
What are the restrictions that views have to follow? : SQL Server Architecture
Thanks to some maintenance being done, the sql server on a failover cluster needs to be brought down. How do you bring the sql server down?
Can we use max in where clause?
How to remove duplicate rows from table?
What is difference between table aliases and column aliases? Do they affect performance?
How many categories of data types used by sql server?
Can a stored procedure call itself or recursive stored procedure? How many levels sp nesting is possible?
Is it possible for a stored procedure to call itself or recursive stored procedure?
How to make a column nullable?
What is the command used to recompile the stored procedure at run time?
How to get a list of all tables with "sys.tables" view in ms sql server?
How to count rows with the count(*) function in ms sql server?
What do you mean by an execution plan? Why is it used? How would you view it?
Why I can not enter 0.001 second in date and time literals in ms sql server?
Explain what are the authentication modes in sql server?
