SQL query to delete duplicate rows from same table?

suppose there is a employee table containing columns name, id and salary having 2 or more than two rows with same values in all t开发者_Python百科hree rows...then how to write a query to delete duplicate rows..

---

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

---

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

---

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

---

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;

---

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'