Deleting the Duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Deleting the Duplicates

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-19 : 15:09:26

I've found about 800+ duplicate user records using

select Email, Count(*) as Total from Users
Group by Email

I'm stumped on two issues:

1 - How can I reduce the SELECT results by eliminating records that have no duplicates - that is, when count(*)=1?

2 - I'd like to use the result of the query to delete the duplicate record(s) conditionally. There will be (at least) 2 records with the same Email field, but one record will have a field: [date] is NULL and the other record will have [date] is NOT NULL. I have no idea how this query would be constructed using the result of the above SELECT.

Thanks,

SamC

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-19 : 15:33:28

1.) having count(*) = 1
2.) read

<O>

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-19 : 21:20:25

Aged Yak,

I seem to have asked questions which don't give me the answers I need. My fault, not yours.

1 - having count(*) > 1 does eliminate those that do not have duplicates. This is great to know, may be part of the solution.

However in (2) the examples seem to have a different slant on identifying duplicates - every field must be unique:

TEMP TABLES: Select field1, field2, field3
RENAME & COPY: Select distinct *

In my table, there's a UserID (identity field)that I'd like to preserve, and a field "Email" to search for duplicates.

Without enumerating every field in the record, isn't there a way to build on this select statement to select all records what have duplicates?

SELECT Email, Count(*)
FROM Users
GROUP BY Email having Count(*) > 1

It seems there should be a way to contruct a join using the above set to identify all records that have duplicate Email keys.

SamC

Tiwari
Starting Member

18 Posts

Posted - 2002-07-22 : 04:33:27

Read this might be this will help you
/** For deleting duplicates from table
ALTER TABLE TableWithDuplicates ADD EliminateDuplicates_RowID int NOT NULL IDENTITY(1, 1)

DELETE
FROM t1
FROM TableWithDuplicates t1
JOIN(
SELECT EliminateDuplicates_RowID=MAX(EliminateDuplicates_RowID),
Field1,
Field2,
Field3,
Field4
FROM TableWithDuplicates
GROUP BY Field1,
Field2,
Field3,
Field4
HAVING COUNT(*) >= 2
) t2
ON( t1.EliminateDuplicates_RowID <> t2.EliminateDuplicates_RowID
AND t1.Field1 = t2.Field1
AND t1.Field2 = t2.Field2
AND t1.Field3 = t2.Field3
AND t1.Field4 = t2.Field4)

ALTER TABLE TableWithDuplicates DROP COLUMN EliminateDuplicates_RowID

Edited by - tiwari on 07/22/2002 04:35:21

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources