| Subject | Re: [firebird-support] How do find duplicates in a table? |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2016-02-05T08:21:27Z |
If PERSON_ID is unique and you have an index on SOC_SEC_NO, you can find all records with duplicate SOC_SEC_NO this way (ordered by SOC_SEC_NO, so that they are kind of grouped together):
SELECT *
FROM PERSON P
WHERE EXISTS(SELECT * FROM PERSON P2
WHERE P.PERSON_ID <> P2.PERSON_ID
AND P.SOC_SEC_NO = P2.SOC_SEC_NO)
ORDER BY P.SOC_SEC_NO
(well, you can do it without an index as well, but then it will be slow if PERSON contains millions of records)
If you want to delete the duplicates, you can e.g. do (this will delete all duplicates, except the one with the lowest PERSON_ID):
DELETE FROM PERSON P
WHERE EXISTS(SELECT * FROM PERSON P2
WHERE P.PERSON_ID > P2.PERSON_ID
AND P.SOC_SEC_NO = P2.SOC_SEC_NO)
2016-02-04 20:09 GMT+01:00 'stwizard' stwizard@... [firebird-support] <firebird-support@yahoogroups.com>:
Greetings All,
How do I form a SQL Select statement that will return which records in my PERSON table have duplicate SOC_SEC_NO.
In other words I need a list of persons where the social security number appears in the database more than once. Some SOC_SEC_NO may be null which I do not care about.
PERSON:
PERSON_ID
SOC_SEC_NO
Any help appreciated,
Mike