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 |
What is buffer cash and log cache in sql server?
What is instead of dml trigger?
How can you hide the sql server instances?
How many triggers you can have on a table?
how to get 25th row in any table in sqlserver can u tell me syntax
What are cursors? Name four types of cursors and when each one would be applied?
Which are ddl commands?
Explain what are the events recorded in a transaction log?
What is the cpu pressure?
What is full outer join in sql server joins?
How can we use ConnectorJ JDBC Driver with MS SQL?
can you any body tell me why are go for the rebuild the master database.what is the reason?