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 cdc in sql server?
here id col have primary key and identity id name 1 a 2 b 3 c 4 d delete 2nd row then o/p will be id name 1 a 3 c 4 d next inssert 2nd row and i want o/p will be id name 1 a 2 e 3 c 4 d
What are the kinds of subquery?
What is enhanced database mirroring in sql server 2008?
How to maintain a fill factor in existing indexes?
What is truncate table?
SQL Server Architecture ?
How many index can be created for single table
4 Answers CarrizalSoft Technologies, Verizon,
What protocol does sql server use?
What is the purpose of optimization?
Difference between Inner vs outer joins?
How to create a testing table with test data in ms sql server?