Deleting Dup Record 2

[Dodge]

SQL God's...I need your help.

I want to delete duplicate records out of one of my tables, but not all of them. For instance if I have 3 records that contain the same [S/N] I want to delete all but one of thoses records.

Any thoughts?

Thanks
Dodge

 

[jjonesal]

I'm not a SQL god for sure (In fact, I'm relatively new to SQL Server), but why don't you create a temporary table (table name starts with #), do a select distinct into that temp table. Then, delete all records in original table, and populate with data from the temp table. Just a thought...

I'm sure there's a way to do this in a SQL statement, but I've got to think on that...

JJ

 

[Dodge]

Thanks JJ, the reason I would like to use an SQL statement is because the table is in constant use, if I do it the way you suggested there would be some down time that the company just hates, or I would have to come in on a weekend to do it, witch I my have to do. But thanks for the suggestion.

Dodge

 

[tlbroadbent]

Here are a couple of methods that should work.

If your table has an identity column or some other unique identifier column (ID) for the records, you could run a query like this.

Delete table
From table a
where Exists
(select * from table
Where SerNo=a.SerNo
and ID<a.ID)

This query will delete any records that match another record with the same SerNo and a lower ID. The record with the lowest ID for each SerNo will remain.

If no unique ID exists, the following script can be used. It is more complex and will take longer but should do the job.

/* Create a temp table with all the columns of the original table but insert no records. */
Select * Into #temp
From table
Where 1=2

/* Create a unique index on the temp table */
Create Unique Index indxname On #temp (SerNo)

/* insert one of the duplicated records in the temp table. */
Insert #temp
Select distinct a.*
From table As a
Inner Join
(Select SerNo, count(*) As RecCnt
From table
Group By SerNo
Having count(*) > 1) as b
On a.SerNo=b.SerNo

/* Delete all of the duplicated records from the original table */
Delete table
Where exists (select * From #temp where SerNo=table.SerNo)

/* Insert the single records from the temp table into the original table */
Insert table
Select * From #temp

/* Drop the temp table */
Drop Table #temp Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jjonesal]

Okay - I know you already have a solution from tlbroadbent, but this was really bugging me about how to do this in one sql statement....still couldn't get it to one statement, but here's a solution that does not use a temporary table. I tested it and I think it works. The go batch separators are required otherwise once you add the identity column, it won't be visible with the delete). Assume: table1 is your table name & your field names in the table are test & test2.

Dodge, does this qualify me as a SQL god? I had to really think on this one.

alter table table1
add uniqueid int identity(1,1)
go

delete from table1
where uniqueid in (
select distinct y.uniqueid
from table1 x, table1 y
where x.test = y.test and
x.test2 = y.test2 and
x.uniqueid < y.uniqueid)

go
alter table table1 drop column uniqueid

 

[Dodge]

You both qualify as SQL God's! Or at least to me because I sure could'nt figure it out. Thanks for all the help.
Dodge