De-dups

[JustWondering]

Please take a look at this code and tell me what it does?
If there are 2 records with the same fieldx & fieldy, will they be remove both or just one?

Thanks.

-------
declare
@fieldx char(5),
@fieldy char(5)

declare the_cursor cursor for
select fieldx,fieldy
from Table1

open the_cursor

fetch from the_cursor into @fieldx,@fieldy

while @@FETCH_STATUS = 0 begin
delete tabel1 where fieldx=@fieldx and fieldy=@fieldy
fetch from the_cursor into @fieldx,@fieldy
end
close the_cursor
deallocate the_cursor
------------

 

[SonOfEmidec1100]

I reckon if you correct the typo it will delete all the rows in the table.

 

[JustWondering]

Will it delete all the rows or will it keep one?

Sorry, my English is very bad.

 

[SonOfEmidec1100]

The query will delete every row in the table, not just those where there are duplicates. The same as if you used

TRUNCATE TABLE table1
or
DELETE FROM table1

 

[JustWondering]

So how can I fix that? I don't want it to delete all the row.

 

[SonOfEmidec1100]

Its quite difficult unless the table has a unique ID, an identity seq field is ideal. If neccessary you could just add one to the table, and remove it later, or maybe create a temp table.

I based this on something in the FAQs faq183-2682, there are probably other solutions - use the search engine.

I used something like this to identify duplicates, to delete them just change the SELECT * to DELETE, once you are happy its right. Maybe take a backup first ?

SELECT * FROM Table1
WHERE EXISTS ( SELECT NULL FROM Table1 b
WHERE
b.fieldx = Table1.fieldx
AND b.fieldy = Table1.fieldy
GROUP BY
b.fieldx, b.fieldy
HAVING
Table1.uniqueid < MAX(b.uniqueid))

 

[JustWondering]

Thank you.