how to delete duplicate rows from table
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
Answer / kumar.t
Navaneethakrishnan your query only delete id 1 but not
delete remaining records.
Is This Answer Correct ? | 3 Yes | 1 No |
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 |
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 |
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 |
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 |
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 |
What are statistics, under what circumstances they go out of date, how do you update them?
What are the two modes of authentication in sql server?
What does normalization do to data?
What is function of ROLLUP ?
what is mean by constraints in sql?
how will u create "n" possible tables in sql...
What is the difference between a clustered index and a non-clustered index?
What are the steps need to be take improve the speed of a SQLDATA Server?
Write query to return all rows sql?
What are the differences between substr and charindex in sql server.
Tell me the phases a transaction has to undergo?
What is the default isolation level of the sql server database?