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 |
how do you determine the Load performance of any query in sql server {example how do u determine performance of a select stmnt which returns Dynamically many no of records ... some times 100,1000,10000 etc., }
How to download microsoft sql server 2005 express edition?
How do you delete a trigger?
Issues related in upgrading SQL Server 2000 to 2005 / 2008
What do you mean by recursive stored procedure?
What does it mean to normalize a database and why would you do it?
What is clustered primary key?
how can ur insert the emp table
Tell me what are the advantages of using stored procedures?
what are candidate key, alternate key and composite key? : Sql server database administration
what is an extended stored procedure? : Sql server database administration
Explain features and concepts of analysis services?