Using Query to delete duplicate rows

[jcoleman]

Is there any way of using sql-query to delete duplicate rows from a table.

What I would like is to delete all of the records that are displayed when I do a 'Select Distinct'. It's to bad that the distinct does not work with delete.

I was thinking that maybe a suq-query may work but after thinking it though; I can't figure out how I would make it work.

Thanks, for any help.

 

[hneal98]

You can do a select distinct and put that result in a temp table and then run a query that would delete all records that show up in the temp table.

 

[kishleo]

go through

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

Kishore

 

[jcoleman]

Thanks for the help.

But I have another very stupid ?? where the heck do I right this script.

Every thing that I've read tells me about wrighting scripts, but being the complete rookie I have no idea where to wright them or execute them.

Sorry for wasting everyone's time with such a stupid question. I really need to take a course on this stuff.

Thanks,

 

[hneal98]

In SQL Server Query Analyzer.

 

[jcoleman]

Thanks, hneal98

I found it, I am so &*($%^ stupid.

 

[hneal98]

No problem.

 

[karbon]

There is a simple way.
You dont need to use temporary tables.


Easy way
------------------
Example table
------------------
Create table EG
(
ID int,
Value1 int,
Value2 int
)

declare @ID int
declare @Count integer
declare CursorDuplicates Cursor for
SELECT ID FROM EG
open CursorDuplicates
fetch next from CursorDuplicates into @ID
while @@fetch_status=0
begin
select @Count = count(ID) from EG where ID = @ID
if @Count > 1
begin
DELETE EG WHERE CURRENT OF CursorDuplicates
end
fetch next from CursorDuplicates into @ID
end
close CursorDuplicates
deallocate CursorDuplicates

 

[kishleo]

i dont think so this script works for table where multiple columns are duplicate, sicne it is checking only for ID,

Kishore MCDBA

 

[karbon]

Come on
Ofcourse it works.
-----------------------------

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

 

[kishleo]

yes it works, but do you have any idea which one will work better? i mean with temporary tables? or with cursors



Kishore MCDBA