How can I delete duplicate rows

[adasoft]

How can I delete the duplicate rows from a table with the help of a single query?

 

[nigelrivett]

If they are completely duplicate then you can't.
There are lots of ways of running scripts to do it though.
See below for a single query if there is a unique field but duplicates in others

set rowcount 1
select 1
while @@rowcount > 0
delete tbl
from tbl t1
join (select col1, col2, ... from tbl group by col1, col2, ... having count(*) > 1) t2
on t1.col1 = t2.col1 and t2.col1 = t2.col1 and ...

set rowcount 0

For unique fields (ID) but other duplicates

delete tbl
from tbl t1
where exists (select * from tbl t2 where t1.id < t2.id and t1.col1 = t2.col1 and t2.col2 = t1.col2 and ...)



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[kishleo]

here is an article how to handle the duplicate records

http://www.sqlteam.com/item.asp?ItemID=3331

Kishore

 

[adasoft]

I want to delete these duplicate rows from a table in SQL server that has no primary or unique keys.Now I think u have got my exact problem.Please give me the way.

Thanx

 

[royjimenez]

select *
from myTable T
where 1 < ( select count(*)
from myTable
where T.field1 = field1 and
...
)

it's easy to understand, there are others ways to do it more efficiently.

* U shold have a primary key for each table.

Regards.




The life is too short to cry and long enough to try it... God bless us.

 

[kishleo]

dont worry about the keys, just go thruough the article

http://www.sqlteam.com/item.asp?ItemID=3331

and you can handle it.

Kishore

 

[karbon]

The way that i use.
You can delete fully or partly duplicated
------------------
Example table
------------------
Create table EG
(
ID int,
Value1 int,
Value2 int
)


declare @ID int
declare @Value1 int
declare @Value2 int
declare @Count integer
declare CursorDuplicates Cursor for
SELECT ID FROM EG
open CursorDuplicates
fetch next from CursorDuplicates into @ID , @Value1 , @Value2


while @@fetch_status=0
begin
select @Count = count(ID) from EG where ID = @ID
and Value1 = @Value1
and Value2 = @Value2

if @Count > 1
begin
DELETE EG WHERE CURRENT OF CursorDuplicates
end
fetch next from CursorDuplicates into @ID , @Value1 , @Value2
end
close CursorDuplicates
deallocate CursorDuplicates