get all the duplicates, keep 1 row of them, and then delete the other duplicates

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)

get all the duplicates, keep 1 row of them, and then delete the other duplicates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-17 : 06:41:22

Nick writes "I was trying to basically scan through a table and delete duplicate records. But I do want one of the duplicates to remain.

I ran across your post here:
http://www.4guysfromrolla.com/webtech/sqlguru/q051200-2.shtml

I used the SQL code, however it deletes ALL my duplicates and does not leave one row. I'm not sure if it's your SQL code, or if it's how I changed it to fit my table. Would you be so kind as to take a look at this please? I am very thankful.

Table Design:

Colums= irecordid, iuserid, ithreadid, vchtype

The irecordid column is the primary key.

Here is the transformed SQL statement:

DELETE
FROM ftdb_subscription
WHERE ftdb_subscription.irecordid IN

(SELECT F.irecordid
FROM ftdb_subscription AS F
WHERE Exists (SELECT iuserid, ithreadid, vchtype, Count(irecordid)
FROM ftdb_subscription
WHERE ftdb_subscription.iuserid = F.iuserid
AND ftdb_subscription.ithreadid = F.ithreadid
AND ftdb_subscription.vchtype = F.vchtype
GROUP BY ftdb_subscription.iuserid, ftdb_subscription.ithreadid, ftdb_subscription.vchtype
HAVING Count(ftdb_subscription.irecordid) > 1))

AND ftdb_subscription.irecordid NOT IN

(SELECT Min(irecordid)
FROM ftdb_subscription AS F
WHERE Exists (SELECT iuserid, ithreadid, vchtype, Count(irecordid)
FROM ftdb_subscription
WHERE ftdb_subscription.iuserid = F.iuserid
AND ftdb_subscription.ithreadid = F.ithreadid
AND ftdb_subscription.vchtype = F.vchtype
GROUP BY ftdb_subscription.iuserid, ftdb_subscription.ithreadid, ftdb_subscription.vchtype
HAVING Count(ftdb_subscription.irecordid) > 1)
GROUP BY ftdb_subscription.iuserid, ftdb_subscription.ithreadid, ftdb_subscription.vchtype);"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-17 : 07:12:26

why don't you simply use a union on your table put the reesults of that in a temp table truncate the original table and put the corrected data from the temp table into an original table.

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-17 : 09:08:52

that is definitely NOT our article, not sure why you think we wrote that!

see if this helps:
http://weblogs.sqlteam.com/jeffs/archive/2004/11/02/2460.aspx

Also, search the articles HERE at sqlteam for more on deleting duplicates. Accept no substitutes from other sites!!

- Jeff

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-17 : 10:16:56

I had to do a similar thing a while back. It took me quite a bit of searching, but I came up with this solution. To be honest, I cannot really remember how I did it, but this query extracts duplicate rows into another table. I think I altered the statement to delete the rows, but ran this first as a back up.

select distinct J36.CycleInstanceId, J36.AccountNo, J36.MobileNo into J36Dupes
FROM J36 INNER JOIN
(SELECT distinct CycleInstanceId, AccountNo, MobileNo
FROM J36
GROUP BY CycleInstanceId, AccountNo, MobileNo
HAVING Count(*) > 1
) AS Duplicates
ON J36.CycleInstanceId = Duplicates.CycleInstanceId
AND J36.AccountNo = Duplicates.AccountNo
AND J36.MobileNo = Duplicates.MobileNo

Hearty head pats

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-17 : 12:43:51

But they give the site a promo

http://www.4guysfromrolla.com/webtech/faq/Databases/faq8.shtml

If they are pure duplicates, then SELECT DISTINCT should do the trick..

But of course they're not.

Right.

You then need to figure out which ones to keep, and why.

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-17 : 12:51:03

Hey, even in their boards, they reference the site

http://www.aspmessageboard.com/forum/databases.asp?M=748419&T=748391&F=21&P=1

Brett

8-)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-20 : 05:10:04

quote:

For tons of great SQL Information, also be sure to check out: SQLTeam.com!

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources