Delete Duplicate Child-Parent(s) Relationships

A user reported that having imported the ancestors of a certain person from FamilySearch through RootsMagic 11 that a large number of children ended up showing duplicate parents in RM but not on FamilySearch. He sought a way to eliminate them, other than by finding and fixing every instance, one-at-a-time, through RootsMagic. (see Community Forum)

Gemini Flash (fast) generated the following queries with very little direction from me. They should be compatible with all versions from RM4 to RM11.

Identify

The first and more complex one identifies the duplicates, as in this example using SQLiteSpy:

SELECT 
    c.ChildID,
    cn.Given AS Child_Given,
    cn.Surname AS Child_Surname,
    f.FamilyID,
    fn.Given AS Father_Given,
    fn.Surname AS Father_Surname,
    mn.Given AS Mother_Given,
    mn.Surname AS Mother_Surname,
    COUNT(*) AS Duplicate_Count,
    GROUP_CONCAT(c.RecID) AS ChildTable_RecIDs
FROM ChildTable c
-- Join primary name for the child
LEFT JOIN NameTable cn 
    ON cn.OwnerID = c.ChildID 
   AND cn.IsPrimary = 1
-- Join family record
JOIN FamilyTable f 
    ON c.FamilyID = f.FamilyID
-- Join primary name for the father
LEFT JOIN NameTable fn 
    ON fn.OwnerID = f.FatherID 
   AND fn.IsPrimary = 1
-- Join primary name for the mother
LEFT JOIN NameTable mn 
    ON mn.OwnerID = f.MotherID 
   AND mn.IsPrimary = 1
GROUP BY c.ChildID, c.FamilyID
HAVING COUNT(*) > 1;

The above script is not needed to clean out the duplicates but may be of value in analysing how they came about before deletion.

Delete

This second query is the tool for bulk deletion of the duplicates and is short and sweet:

DELETE FROM ChildTable
WHERE RecID NOT IN (
    SELECT MIN(RecID)
    FROM ChildTable
    GROUP BY ChildID, FamilyID
);

Prompting Gemini

In the previous couple of years, I gave AI very detailed directions including the key data definitions I knew it would need. This year, I have found that they have learned a lot about RootsMagic databases and need less direction in the user prompts. Gemini got the second query right on its first attempt but needed correction for the first query. Below are the two prompts I gave it:

A RootsMagic database has unwanted duplicate child-parents relationships. Generate a SQLite query that identifies the children and the parents. Then generate a second query that deletes the duplicates. Do you need me to tell you the objects in the database that need to be involved in these queries? 

It said it had all the information it needed but proceeded on the wrong assumption of where the names of the child and parents are stored, so…

Your first query fails because it mistakenly assumes that the person’s names are found in the PersonTable. Instead they are found in the FamilyTable. While the PersonTable generates the PersonID key which relates to other tables, its information about the person is limited to Sex and a Note. The rest of it is primarily registers in support of the user interface. Do you need direction on retrieving the primary name values from NameTable?

That corrected the Identify query and Gemini asked, “Would you like a query to check for orphan records or integrity issues after running the delete?”

Yes, I am curious to see what orphans and integrity issues you would check for and how you would do it.

It responded with several which I will simply list because I’ve not tested them:

1. Database Engine Health Checks
2. Orphaned ChildTable Records (Dangling Links)
3. Orphaned Parent References in FamilyTable
4. “Ghost” Families (Empty Family Containers)
5. Logical Anomaly: Child as Their Own Parent

It then asked “Want me to generate automated DELETE queries for any ghost families or orphaned child records found by these checks?” Yes and then it produced three plus a consolidation of the three, all untested:

1. Delete Orphaned ChildTable Records
2. Delete “Ghost” Families
3. Repair Invalid Parent References in FamilyTable
4. Complete Safe Execution Script