Remove duplicates

[robert030975]

I'll be here sometime removing this line by line if anyone could help come up a different way PLEASE!!

-- list of dups
SELECT store, purId, week, COUNT(*) AS countdup
FROM dbo.f_purchase
GROUP BY store, purID, week
HAVING (COUNT(*) > 1)
order by purID, week, store

- set var
Declare @store as varchar(4), @week as varchar(4), @cid as varchar(2), @uid as int
set @store = 'B673'
Set @week = '2406'
Set @cid = '1'
set @uid = 325004
Select * from dbo.f_purchase
where store = @store AND week = @week and purID = @cid


Delete from f.purchase
where store = @store AND week = @week and purID = @cid AND uid > @uid

 

[bborissov]

Which record from all you want to stay?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Catadmin]

And is there any way, such as an identity field, to tell the difference between your duplicate and your original records?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[robert030975]

Thanks for the quick interest!!

Lets start with the first select brings back

store purID week count
B516 1 2506 2
B664 1 2606 2
B583 1 2706 2
B740 1 2706 2
B693 1 2806 2
B727 1 2906 2
B753 1 2906 2
B304 1 3006 2
B560 1 4305 2
B616 1 4905 2
B771 10 2006 3
B727 10 2206 2
B748 10 2306 2
B763 10 2306 2
B727 10 2406 3
B750 10 2506 2
B741 10 3005 2

if I do the second part eg B516

I get

UID Store purID week sv cf
326688 B516 1 2506 2216.0000 100
326689 B516 1 2506 2216.0000 100

So take your pick which UID to delete!

just to complicate matters I have spotted the odd one which doesn't have the same value.

 

[bborissov]

Thinks doesn't happen that way
If you want automatic delete you must have at least one field which made the record unique. If I issue DELETE .... agains that table ALL duplicated records will be deleted, no records will be present in table anymore. So the quesion is WHAT makes the records different. If this is a one time job, maybe I could think of some different approach, but if this is a regular job, you must made records unique.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[robert030975]

To be honest Boris Ive just discovered it so I have now stopped the .net app from allowing users to keyin dup entrys

apart from the UID the record is the same I can't think of how it can be identified so I'm open to ideas

 

[SgtJarrow]

I am not a SQL guru...so my code suggestions would mean nothing. But (and that is always the next word, isn't it) if I follow your logic, this is a one-off fix.

So why not create a temp table off the second-part table that uses SELECT DISTINCT, minus the UID field. Truncate the second-part table and then put the temp table records back into the second-part table.

This should effectively remove all duplicate entries...and you have stated you have fixed your app to prevent future occurances.

Hope this helps if I uhave understood the problem correctly.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Catadmin]

Try this:

SELECT A.UID, fp.store, fp.purId, fp.week
FROM dbo.f_purchase fp
join (Select Max(UID), Store, PurID, Week
      from f_purchase
      GROUP BY store, purID, week
      HAVING (COUNT(*) > 1)) A
on fp.UID = A.UID
order by purID, week, store

Delete from f_purchase
FROM dbo.f_purchase fp
join (Select Max(UID), Store, PurID, Week
      from f_purchase
      GROUP BY store, purID, week
      HAVING (COUNT(*) > 1)) A
on fp.UID = A.UID

Something on that order should work. I do it all the time. Make sure to select your records, though, before doing the delete to verify the code works correctly. And backup your DB before you do this in case you need to restore.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[robert030975]

Cheers chaps!

Always learning new ways in sql... as the saying goes there is more than one way to skin a cat!

 

[robert030975]

Hi catadmin whats


ran the code
SELECT A.UID, fp.store, fp.purId, fp.week
FROM dbo.f_purchase fp
join (Select Max(UID), Store, PurID, Week
from f_purchase
GROUP BY store, purID, week
HAVING (COUNT(*) > 1)) A
on fp.UID = A.UID
order by purID, week, store


No column was specified for column 1 of 'A'.??

 

[Catadmin]

Oh, sorry. You have to alias MAX(UID). I keep forgetting that. @=)

So take Select Max(UID), Store, PurID, Week
and make it Select Max(UID) as UID, Store, PurID, Week.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[robert030975]

Hi Catadmin

do you have to alias the rest eg store, purID, week
as I 'm getting Ambiguous column name?

 

[robert030975]

yes I do!!!

Thanks catadmin

really appreciate the help saved me many hours and i get to go home @ hometime!!!

 

[gmmastros]

Change
order by purID, week, store

To:

order by [!]fp.[/!]purID, [!]fp.[/!]week, [!]fp.[/!]store

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Glad I could help.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ESquared]

Catadmin,

Your query works great but only deletes a single duplicate if there are more than two.

Here's a way to delete all duplicates, no matter how many:

DELETE fp
FROM
  dbo.f_purchase fp
  INNER JOIN (
     SELECT nUID = Min(UID), Store, PurID, Week
     FROM f_purchase
     GROUP BY Store, PurID, Week
     HAVING COUNT(*) > 1
  ) A ON fp.Store = A.Store AND fp.PurID = A.PurID AND fp.Week = A.Week AND fp.UID > A.nUID

[/code]