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
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / sumathy
Execute the following query for each duplicate value.
1.Delete duplicate of empname='A'
delete top(select count(*)-1 from employee where
empname='A') from employee where empname='A'
2.Delete duplicate of empname='D'
delete top(select count(*)-1 from employee where
empname='D') from employee where empname='D'
Is This Answer Correct ? | 9 Yes | 2 No |
Answer / honey
with myCTE as(
select row_number() over( partition by empname order by
empname) as myCount from Employee
)delete from myCTE where myCount >1
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / 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 |
Answer / pradeep
DECLARE @TEMP_TABLE TABLE (EMPNAME VARCHAR(10),SALARY
VARCHAR(10))
INSERT INTO @TEMP_TABLE (EMPNAME ,SALARY )
SELECT 'A','200' UNION ALL
SELECT 'B','300' UNION ALL
SELECT 'C','400' UNION ALL
SELECT 'A','200' UNION ALL
SELECT 'A','200' UNION ALL
SELECT 'D','500' UNION ALL
SELECT 'D','500' UNION ALL
SELECT 'B','300'
/* TABLE RAW DATA */
SELECT * FROM @TEMP_TABLE
SELECT * FROM @TEMP_TABLE AS A
WHERE (SELECT COUNT(*) FROM @TEMP_TABLE AS B WHERE
A.EMPNAME=B.EMPNAME AND A.SALARY=B.SALARY) > 1
/* OUTPUT SHOULD BE */
SELECT DISTINCT * FROM @TEMP_TABLE
/* DELETE DUPLICATE/TRIPLICATE.... RECORDS */
SET ROWCOUNT 1
DELETE @TEMP_TABLE FROM @TEMP_TABLE AS A
WHERE (SELECT COUNT(*) FROM @TEMP_TABLE AS B WHERE
A.EMPNAME=B.EMPNAME AND A.SALARY=B.SALARY) > 1
WHILE @@ROWCOUNT > 0
DELETE @TEMP_TABLE FROM @TEMP_TABLE AS A
WHERE (SELECT COUNT(*) FROM @TEMP_TABLE AS B WHERE
A.EMPNAME=B.EMPNAME AND A.SALARY=B.SALARY) > 1
SET ROWCOUNT 0
/**********************************************/
/* EXPECTED OUTPUT TABLE */
SELECT * FROM @TEMP_TABLE
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / 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 |
Answer / devender kumar
This query is for sql server 2005 and higher version of sql
server. It will not run on older versions.
with myCTE as(
select row_number() over( partition by empname order by
empname) as myCount from Employee
)delete from myCTE where myCount >3
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / gaurav sharma
SELECT DISTINCT * INTO Employee1 FROM Employee
DROP Table Employee
sp_rename 'Employee1','Employee'
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / samba shiva reddy . m
delclare @temp(empname varchar(50),sal int)
select * into @temp
FROM employee
GROUP BY empname, salary
HAVING count(*) > 1
delete from employee
select * into employee from @temp as temp
Is This Answer Correct ? | 0 Yes | 1 No |
In which year relase the 7.0& 2000 & 2005?
Is foreign key unique?
What are ddl triggers and types of ddl trigger?
Can we use pragma autonomous_transaction in trigger?
What are the two types of concurrency?
How do I debug a stored procedure in sql server?
What is ems sql management studio? : sql server management studio
What do you understand by triggers and mention the different types of it?
Your sql server is running out of disk space. You notice that there are several large files with ldf extensions what are these files?
Explain a join?
How the data stores in a page?
What is the purpose of data source?