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 |
Define candidate key, alternate key, composite key.
13 Answers Infosys, Software India,
How to find the date and time of last updated table?
what is the order of execution of where,having,group by in select stement
6 Answers IBM, Tanla Solutions,
If I delete a template from the list in sql studio, will it be deleted from the hard disk? : sql server management studio
Explain the advantages of merge replication?
What gets stored inside msdb database?
What does it mean to normalize a database and why would you do it?
Is mysql better than sql server?
what is bit datatype and what's the information that can be stored inside a bit column? : Sql server database administration
What is raid, and how it can influence database performance?
You want to implement the one-to-many relationship while designing tables. How would you do it?
how to copy sysdatabase file from c: Drive to D: Drive in SQL Server.