suppose there is a employee table containing columns name, id and salary having 2 or more than two rows with same values in all three rows...then how to write a query to delete duplicate rows..
-
2Is this a homework question? It would help us if you specified which database you are using - as the syntax could differ.rlb.usa– rlb.usa2010-12-22 15:20:31 +00:00Commented Dec 22, 2010 at 15:20
-
1Curious, what is the primary key of this table? Since typically most will use "ID" as the primary key. If "ID" is the primary key. Then how do you have duplicates? Sounds like a issue with your Primary Key constraint.John Hartsock– John Hartsock2010-12-22 15:22:32 +00:00Commented Dec 22, 2010 at 15:22
-
Sounds more like how to find duplicate questions on SO search: +delete +duplicatebernd_k– bernd_k2010-12-22 15:31:10 +00:00Commented Dec 22, 2010 at 15:31
Add a comment
|
5 Answers
Here is a nice way if you use Sql Server
with duplicates as
(select * ,ROW_NUMBER() over(
partition by id,name, salary
order by id,name, salary) rownum
from Person)
delete from duplicates where rownum > 1
1 Comment
bernd_k
+1 the comment is for me to increase the chance to find the link again, when I need it.
assuming ID is the primary key:
delete P
from Person P right outer join
(
select name, min(id) as id
from Person
group by name
) unique_people
on P.id = unique_people.id
where P.id is NULL
Comments
u can set unique key to to your field ... otherwise you can delete all the duplicate row by
delete from table_name where id=@id and name=@name
Comments
Insert the distinct rows from the original table to new temporary table. Delete data from original duplicate table then insert the distinct rows from the temporary table to original table.
select distinct * into temp From Emp;
delete from Emp;
insert into Emp(name,ID,salary) as select * from temp;
drop table temp;
Comments
DELETE TOP(1) FROM tablename WHERE columemane='name'
Supposing I have 2 duplicate rows in table student:
name | number | last name
Manoj | 1125256 | Sharma
Manoj | 1125256 | Sharma
I want to delete one using the following query
DELETE TOP(1) FROM student WHERE name='Manoj'
1 Comment
Andrea Ligios
This works only when you have two rows, not if you have more duplicate records.
