Subject: | Checking for duplicates
| Date: | Fri, 20 May 2016 14:49:31 +1000
| From: | Tom Krieg <invalid@nodomain.tv>
| Newsgroups: | pnews.paradox-client_server
|
This has me beat. I have 2 tables, address_detail and address_locality.
fields in address_detail include a PKey = address_detail_uid (int),
locality_uid (int), address_postcode (character(4)) and other stuff.
address_locality including a PKey = locality_uid (integer).
There is a foreign key in address_detail linked --> locality_uid.
There are no address_detail rows with a NULL locality_uid, so each
locality in address_detail has a matching locality record.
My task is to transfer postcode from address detail to locality. There
are many address details with the same locality and postcode.
Step 1: I get the number of distinct localities in address_detail.
SELECT DISTINCT locality_uid FROM address_detail;
15,195 localities
Step 2: I get the number of localities in the locality table
SELECT locality_uid from locality;
16,398 localities
OK, so there are a number of localities which don't have addresses.
That's OK because there are a lot of parks, fields, beaches etc etc in
there as localities.
Step 3: I now get the number of distinct locality/postcode
combinations in the address table, to see if the total
localities match.
SELECT DISTINCT locality_uid, address_postcode FROM address_detail;
17,824 rows
So we have a number of localities with more than 1 postcode. Now I want
to determine which ones.
Step 4: Check for duplicate postcodes.
SELECT foo.address_postcode, foo.locality_uid, COUNT(1) as COUNT
FROM
(SELECT DISTINCT address_detail.address_postcode,
address_detail.locality_uid
FROM address_detail) AS foo
GROUP BY foo.address_postcode, foo.locality_uid
HAVING COUNT(1) > 1
;
0 Rows
Can someone tell me where are the duplicates?
|