Duplicate problem - 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

Transact-SQL (2000)

Duplicate problem

Author

Topic

MarcLaferriere
Starting Member

4 Posts

Posted - 2002-11-29 : 14:58:00

what i'm trying to do is:
> Group the PhoneSearch Records together and check for duplicates.
>
> (THIS WORKS)
> UPDATE COPYtblCustomers SET ToDelete = 1
> FROM COPYtblCustomers
> WHERE MasterID in (SELECT MasterID
> FROM COPYtblCustomers
> WHERE PhoneSearch IN ( SELECT PhoneSearch
> FROM COPYtblCustomers
> Group BY PhoneSearch
> HAVING (Count(PhoneSearch) > 1)))
>
> --> So when there is duplicate records(Same phonesearch #) I set
> ToDelete to 1. (query above)
>
>
> Now that all Duplicate Fields are set to 1 in 'ToDelete', I need to
> set ToDelete equal to '2' for the Minimum MasterID of the Duplicates.
> (AKA I want to keep the records that are set to '2' because they are
> the Original records and delete all the other duplicates (records
> which equal '1').
>
> This is what I have:
> It's only giving me 1 row for a result, which obviously isnt' what I"m
> looking for.
>
> UPDATE COPYtblCustomers SET ToDelete = 2
> FROM COPYtblCustomers
> WHERE ToDelete = 1 AND MasterID = (SELECT MIN(MasterID)
> FROM CopytblCustomers
> WHERE PhoneSearch IN ( SELECT PhoneSearch
> FROM copytblcustomers GROUP BY PhoneSearch))
>
>
> Thanks Guys!

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-29 : 16:43:45

As MichaelP's signature states
<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

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

-----------------------
SQL isn't just a hobby, It's an addiction

MarcLaferriere
Starting Member

4 Posts

Posted - 2002-11-29 : 16:56:01

Interesting, That's not really helping my problem though.
any other tips?

thanks in advance

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-12-01 : 11:51:52

update copytblcustomers set todelete = 2 where todelete = 1 and masterid in(
select min(masterid) from copytblcustomers group by phonesearch)

Sarah Berger MCSD

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-01 : 12:15:02

You can do everything in 1 step.

When you are grouping by PhoneSearch, you have access not only to the COUNT() for that group, but also the MIN() of MasterID for that group.

Thus, you want to delete all records where the COUNT is greater than 1, as long as the MasterID is not the MIN of MasterID.

So, the list of ID's to delete is:

SELECT C.MasterID
FROM
COPYTblCustomers C
INNER JOIN
(SELECT PhoneSearch, Min(MasterID) as MinID, COUNT(*) as Num FROM COPYTblCustomers GROUP BY PhoneSearch) A
ON
C.PhoneSearch = A.PhoneSearch
WHERE
C.MasterTableID <> A.MinID AND
Num > 1

Check it out, see if it makes sense. Do everything in 1 pass if at all possible.

Since that provides a list of MasterID's to delete, you can then do it all in one step, without having to use update queries and such:

DELETE FROM
COPYTblCustomers
WHERE
MasterID IN (...above SQL....)

Let me know if it works. Even if you already used another solution, the above may be an easier way to handle this sort of thing.

- Jeff

Edited by - jsmith8858 on 12/01/2002 13:34:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources