April 9, 2002 at 3:52 pm
How do I Delete duplicate records in my DB using a column such as ZIp code or
April 9, 2002 at 4:04 pm
hopefully you have some other pk that you can use. Match up on the dup field and pick the min or max of hte pk.
If you do not have this, then you can basically get dups with something like
select
zip
, count(*)
from zipcodes
group by zip
having count(*) > 1
Use this to find which are dups and you can set rowcount 1 and delete a occurnace of the dup. loop over this until you get them all.
Post a try and we'll try to help.
Steve Jones
April 9, 2002 at 6:47 pm
This is what I have so far but when I run it it deletes all the records. What am I doing wrong
DELETE
FROM Customers
WHERE Customers.CUSTID IN
(SELECT Customers.CUSTID
FROM Customers AS F
WHERE EXISTS
(SELECT CUSTID, COUNT(CUSTID)
FROM Customers
WHERE Customers.CUSTID = Customers.CUSTID
GROUP BY Customers.CUSTID
HAVING
COUNT(Customers.CUSTID) > 1))
(SELECT MIN(CUSTID)
FROM Customers AS F
WHERE EXISTS
(SELECT CUSTID, COUNT(CUSTID)
FROM Customers
WHERE Customers.CUSTID = Customers.CUSTID
GROUP BY Customers.CUSTID
HAVING
COUNT(Customers.CUSTID) > 1)
GROUP BY CUSTID);
April 9, 2002 at 11:47 pm
if this is a one-time job you can add a identity column and run this query
DELETE Customers
FROM Customers
WHERE Identid NOT IN
(
SELECT Min(Identid)
FROM Customers
GROUP BY CUSTID
)
April 10, 2002 at 8:40 am
The reason you are having problems is because of the database structure. There should never be duplicate data. The best long term solution is to redesign the datbase structure to avoid duplicate data.
🙂 good luck
April 11, 2002 at 8:10 pm
That would be good in a normal world but when your talking about a DATA Warehouse and a Half dozen or more DTS Packages, on top of 100's of data source text files that change everday there's going to be duplicates. There is no way to avoid that. Beleive me there is a bigger picture here and that is large amounts of incoming data.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply