--Names-AltMarriedDupeDelete.sql
/* 2017-02-05 Tom Holden ve3meo

Deletes duplicates of Alt Names of type Married
*/


DROP VIEW IF EXISTS vDupAltName
;
CREATE TEMP VIEW  vDupAltName AS
SELECT NameID, OwnerID, Surname, Suffix, Prefix, Given, Nickname, COUNT()-1 AS Dupes -- Dupes >0 means there is a duplicate alt name or more
FROM NameTable 
WHERE NOT IsPrimary  -- excluding primary names from the test for duplicates
AND NameType = 5  -- only Married names
GROUP BY OwnerID, Surname, Suffix, Prefix, Given, Nickname -- grouping gives the count of duplicate alt names
ORDER BY COUNT() Desc -- so we see the duplicate count at the top of the list
;

DROP VIEW IF EXISTS vDeleteAltName
;
CREATE TEMP VIEW  vDeleteAltName AS
SELECT N.NameID 
FROM NameTable N
JOIN vDupAltName D USING (OwnerID)
WHERE NOT N.IsPrimary
AND N.NameType = 5 -- Married name type
AND D.Dupes  -- must be >0
EXCEPT SELECT NameID FROM vDupAltName ORDER BY NameID
;

DELETE FROM NameTable 
WHERE NameID
IN (SELECT NameID FROM vDeleteAltName ORDER BY NameID)
;