3

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..

skaffman
  • 398,947
  • 96
  • 818
  • 769
Trupti
  • 597
  • 4
  • 8
  • 17
  • 2
    Is this a homework question? It would help us if you specified which database you are using - as the syntax could differ. – rlb.usa Dec 22 '10 at 15:20
  • 1
    Curious, 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 Dec 22 '10 at 15:22
  • Sounds more like how to find duplicate questions on SO search: +delete +duplicate – bernd_k Dec 22 '10 at 15:31

5 Answers5

8

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
Jahan Zinedine
  • 14,616
  • 5
  • 46
  • 70
3

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
davek
  • 22,499
  • 9
  • 75
  • 95
0

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

Vaimin
  • 7
  • 1
  • 6
0

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;
Saifee Vohra
  • 36
  • 7
-1
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'
Raul Rene
  • 10,014
  • 9
  • 53
  • 75
Saloni
  • 11