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
- 14 Answers
- 18483 Views
- I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
Do you know what is replace and stuff function in sql server?
How to stop a loop early with break statements in ms sql server?
you accidentally delete the msdb database what effect does this have on your existing sql databases, and how do you recover? : Sql server administration
Explain Geography datatype in SQL Server
Do you know what is a with(nolock)?
What are sp_configure commands and set commands?
When would you use a before or after trigger?
What do I need to start working with sql studio? : sql server management studio
What is the cartesian product of table?
Explain the difference between delete,drop and truncate in SQL Server?
What are the parts of a function?
Explain different types of lock modes in sql server 2000?
What is a DBMS, query, SQL?
What is the partitioning method?
How to truncate the log in sql server 2012? : sql server database administration
