DECLARE @TEMP_TABLE TABLE (EMPNAME VARCHAR(10),SALARY VARCHAR(10)) INS

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 / pradeep

14 Answers
22291 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

Please Help Members By Posting Answers For Below Questions

What are constraints?

1018

What is the use of floor function in sql server?

1086

What are parameterized reports?

204

Can foreign key be duplicate?

1069

Do you know the isolation level that sql server support?

965

What is relationship? What number of sorts of relationship are there?

1143

How many types of schemas are there?

1044

What is an indice?

1043

You have a table ‘test’ which is a copy of northwind employee table you have written a trigger to update the field ‘hiredate’ with the current date

1057

What does nvl stand for?

947

What is sql server agent and what are the two modes of authentication in sql server?

1061

How to create user defined functions with parameters?

1243

how you can get the list of largest tables in a database? : Sql server administration

1058

what changed between the previous version of sql server and the current version? : Sql server database administration

1016

When would you use an insert into .. Select option versus an insert into .. Values option? Give an example of each?

1113