how to delete duplicate rows from table

Categories >> Software >> Databases >> SQL Server
Suggest New Category

Question Posted / bhaskar

9 Answers
13752 Views
CSC, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to delete duplicate rows from table..

Answer / kumar.t

Name : New

Sno Name
1 Rajesh
2 Rajesh
3 Raja
4 Raja
5 Arun
6 Bala

Delete From New Where Sno Not IN
(Select Min(Sno) From New Group By Name)

Is This Answer Correct ?

14 Yes

0 No

how to delete duplicate rows from table..

Answer / anand

TABLENAME: testanand
DATA:
id vanme emailid age
-- ------ ---------- ------
1 Anand anand.kv@abc.com 29
2 Anand anand.kv@abc.com 28
3 Rajesh rajesh@abc.com 30
4 Rajesh rajesh@abc.com 31
5 Vinit vinit@abc.com 21
6 Vinit vinit@abc.com 25

DELETE FROM testanand WHERE id Not IN
(SELECT min(id) FROM testanand GROUP BY vname)

RESULTS:

id vanme emailid age
-- ------ ---------- ------
1 Anand anand.kv@abc.com 29
3 Rajesh rajesh@abc.com 30
5 Vinit vinit@abc.com 21

Is This Answer Correct ?

8 Yes

1 No

how to delete duplicate rows from table..

Answer / divya mahendra sikarwar

Execute queries:

CREATE TABLE dbo.Test1 (
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO Test1 VALUES(1, ‘Bob’,'Smith’)
INSERT INTO Test1 VALUES(2, ‘Dave’,'Jones’)
INSERT INTO Test1 VALUES(3, ‘Karen’,'White’)
INSERT INTO Test1 VALUES(1, ‘Bob’,'Smith’)
INSERT INTO Test1 VALUES(4, ‘Bobby’,'Smita’)

select identity(int,1,1) as SlNo,* into #temp from Test1

DELETE
FROM #temp
WHERE SlNo NOT IN
(
SELECT MAX(SlNo)
FROM #temp
GROUP BY ID,FirstName,lastname
)

drop table test1

select * into test1 from #temp

alter table test1 drop column SlNo

select * from test1 order by id

Is This Answer Correct ?

6 Yes

0 No

how to delete duplicate rows from table..

Answer / kumar.t

Navaneethakrishnan your query only delete id 1 but not
delete remaining records.

Is This Answer Correct ?

3 Yes

1 No

how to delete duplicate rows from table..

Answer / divya mahendra sikarwar

SELECT FormID,
FormCode,
LangCode,
count(*)

FROM adzForm
GROUP BY FormID,
FormCode,
LangCode
HAVING COUNT(*)>1

Is This Answer Correct ?

2 Yes

0 No

how to delete duplicate rows from table..

Answer / monty

i have other solution if you have table employee with
duplicate rows. You can create other table that doesn't
have duplicate rows

select distinct * into newtable from employee

Is This Answer Correct ?

2 Yes

1 No

how to delete duplicate rows from table..

Answer / gayathri

WITH T1 AS (Select *, ROW_NUMBER() OVER (PARTITION BY id
Order By id) AS rowid From test)

Is This Answer Correct ?

0 Yes

0 No

how to delete duplicate rows from table..

Answer / navaneethakrishnan

Hi Kumar,

Yes it will delete only the id=1, you have to change the
id=2 whichever u want...

DELETE TOP(1) FROM [tblduplicate] WHERE [ID] = 2
DELETE TOP(1) FROM [tblduplicate] WHERE [ID] = 3

For ur kind information,.. it will delete the duplicate
records only if there is exactly 2 duplication...... if the
id 1 will entered to three times, this query will delete
only one record... because

as we enterd the query as top(1)it will select the top
most and delete the record..

This is the simple query to execute.... there is other ways
also.. to do....

Now use this query to do for more than 2 duplication

DELETE TOP(SELECT COUNT(*) -1 from dbo.tblduplicate where
id = 1)
from dbo.tblduplicate
where id = 1

Is This Answer Correct ?

1 Yes

2 No

how to delete duplicate rows from table..

Answer / navaneethakrishnan

table name : tblduplicate

Ans : DELETE TOP(1) FROM [tblduplicate] WHERE [ID] = 1

Example :

CREATE TABLE tblduplicate(ID int NOT NULL, Name varchar(50))

insert into tblduplicate values(1, 'Name1')
insert into tblduplicate values(1, 'Name1')
insert into tblduplicate values(2, 'Name2')
insert into tblduplicate values(2, 'Name2')
insert into tblduplicate values(3, 'Name3')
insert into tblduplicate values(3, 'Name3')

select * from tblduplicate

Result :
1 Name1
1 Name1
2 Name2
2 Name2
3 Name3
3 Name3

DELETE TOP(1) FROM [tblduplicate] WHERE [ID] = 1

Is This Answer Correct ?

1 Yes

7 No

Post New Answer