quick and dirty delete of duplicate db records 2

[ccampbell]

I need a quick way to delete duplicate records out of a database table that has no key values. This table has no relations with other tables as well, it is just a working table from a bulk load insert. I have tried the following

delete from foo where exists (
select null from foo f where f.number = foo.number and
f.type = foo.type and
f.date = foo.date)

but that deletes all the records in my table and not just the duplicate ones. Any help is greatly appreciated.

 

[r937]

quick way? any help?

okay, since there's no key to worry about, might it not just be easier to eliminate duplicates like this:

[tt]select distinct
number, type, date
into newtable
from loadedtable [/tt]


rudy

 

[ccampbell]

Is there a way to do it without creating an additional table?

 

[CurtisJM]

If you are not too space limited and the table is not too large, you could create an identity field and then use it in a correlated subquery:

alter table foo add ident INT identity

delete from foo where ident > (select min(ident) from foo foo2 where foo.number = foo2.number and foo.type = foo2.type and foo.date = foo2.date)

alter table foo drop column ident

 

[ccampbell]

That worked. That is what I was looking for. Thanks CurtisJM.

 

[ccampbell]

One question, I am trying to put the

alter table foo add ident INT identity

delete from foo where ident > (select min(ident) from foo foo2 where foo.number = foo2.number and foo.type = foo2.type and foo.date = foo2.date)

alter table foo drop column ident

statements in with other statements in a stored procedure in SQL I get an error that it does not recognize the column ident when I try to save it. Anyone know of a way around this?

 

[CurtisJM]

Sure... You can throw that delete statement in a variable and then execute the variable...

Create Proc TST AS
declare @cmd varchar(2000)
alter table foo add ident INT identity
set @cmd = 'delete from foo where ident > (select min(ident) from foo foo2 where foo.number = foo2.number and foo.type = foo2.type and foo.date = foo2.date)'
exec(@cmd)
alter table foo drop column ident

 

[ccampbell]

Thanks Curtis! Sometimes the simple things are the hardest to see when you are engrossed in a problem. Thanks for all of your help.