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
22189 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

Can you explain how long are locks retained within the repeatable_read and serializable isolation levels, during a read operation with row-level locking?

979

how to define testing of network layers? : Sql server database administration

969

How to call stored procedure using http soap?

1073

How do I find the sql server version?

1018

What does the on update no action do?

1023

Do you know what are the differences between lost updates and uncommitted dependencies?

1073

What is the main purpose of having conversation group?

896

Can sql servers link to other servers like oracle?

837

How to truncate the log in sql server 2012? : sql server database administration

1280

Explain what are the basic functions for master, msdb, model, tempdb databases?

970

What do you mean by acid?

985

can you implement data mining in SSRS?

148

What is a covering index?

981

What is view in sql?

900

What will happen if a column containing char type data is changed to the nchar data type?

1095