while exists(select count(*) from employee group by empname having count(*)

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

Question Posted / shankaranarayanan v

14 Answers
22259 Views
I also Faced
E-Mail Answers

Answer Posted / shankaranarayanan v

while exists(select count(*) from employee group by empname having count(*)>1)
begin

delete top(1) from employee where empname in
(
select min(empname) as deletedname
from employee
group by empname
having count(*)>1
)

end

Is This Answer Correct ?

1 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

How can we solve concurrency problems?

1055

How to provide values to stored procedure parameters in ms sql server?

1038

Define clusters?

1084

What are three ways you can use an identity value inside a trigger? Why would you prefer one way over another?

1000

How to replace the Query Result 'Null Value' with a text ?

1044

Do you know the policy based administration feature of sql server 2008?

959

Why does sql studio use a single registered database repository? : sql server management studio

1001

Disadvantages of the indexes?

1125

I have triggers,views,functions,stored Procedures for a table. When I am dropping that table which objects are deleted?

1138

What is query optimization process?

1015

Why transaction is important?

976

What is a field in a table?

1041

Explain what are page splits? : SQL Server Architecture

1066

what are the disadvantages of cursors? : Sql server database administration

1030

How to receive output values from stored procedures?

1146