Problem statement

A problem for many users is that, even as of RootsMagic 5.0.4.1, there is still no way of deleting persons from the database other than one at a time. Workarounds using RM to partially export to GEDCOM or partially transfer to a new database lose Named Groups, To-Do lists and truncate long event descriptions with other losses identified in GEDCOM & DnD transfer losses.

Procedures

Two SQLite procedures have been developed as a workaround without the losses attendant to partial export and drag and drop:

1. Delete by Color Coding
2. Delete by Named Group

These depend on the user building the desired set of persons to be deleted having either a specific color code (Red as written) or belonging to a Named Group having a name beginning with “#DELETE#”.

WARNING: The procedures are not reversible and there is no guarantee that the results are perfect or what you may want. Use at your own risk and MAKE A BACKUP first.

Both deletion procedures must be followed by these steps:

1. Delete Phantoms – a procedure that cleans up the database of unused records from most tables left behind by these bulk deletions or by deletions and merges from within RootsMagic. (This is another item on the RM Wish List)
2. RootsMagic > File > Database Tools:
   1. Rebuild Indexes
   2. Compact database
3. RootsMagic > Tools > Count Trees. Inspect and deal with any undesired results.

Scripts

Delete by Color Coding

DeleteByColorCode.sql

-- DeleteByColorCode.sql
/*
2012-10-27 Tom Holden ve3meo
 
Deletes Persons and Families from PersonTable and FamilyTable respectively for
persons with a specified color code.
 
As written, the key color is RED (=1). Edit all instances of "= 1" to the desired code.
 0 = None,
 1 = Red,
 2 = Lime,
 3 = Blue,
 4 = Fuschia,
 5 = Yellow,
 6 = Aqua,
 7 = Silver,
 8 = Maroon,
 9 = Green,
10 = Navy,
11 = Purple,
12 = Brown,
13 = Teal,
14 = Gray
 
N.B.: This procedure results in many types of 'phantoms' and should be followed by
the procedure DeletePhantoms.sql
*/
 
-- Delete Family where one of the spouses has a colorcode match
DELETE FROM FamilyTable WHERE (SELECT Color FROM PersonTable WHERE PersonID = FatherID) = 1;
DELETE FROM FamilyTable WHERE (SELECT Color FROM PersonTable WHERE PersonID = MotherID) = 1;
-- Delete Person from PersonTable if matched on colorcode
DELETE FROM PersonTable WHERE Color = 1;

Delete by Named Group

DeleteByNamedGroup2.sql

-- DeleteByNamedGroup2.sql
/*
2012-10-27 Tom Holden ve3meo
2013-01-21 v2 - deletes groups with name beginning '#DELETE#'
 
Deletes Persons and Families from PersonTable and FamilyTable respectively for
all persons in specific Named Groups having a name starting with #DELETE# .
 
N.B.: This procedure results in many types of 'phantoms' and should be followed by
the procedure DeletePhantoms.sql
*/
-- Delete Family where one of the spouses is in the Group
-- Fathers or Husbands
DELETE
FROM FamilyTable
WHERE FatherID IN (
        SELECT PersonID
        FROM PersonTable
            ,GroupTable
        WHERE PersonID BETWEEN StartID
                AND EndID
            AND GroupID IN (
                SELECT LabelValue
                FROM LabelTable
                WHERE UPPER(LabelName) LIKE '#DELETE#%'
                )
        );
 
-- Mothers or Wives
DELETE
FROM FamilyTable
WHERE MotherID IN (
        SELECT PersonID
        FROM PersonTable
            ,GroupTable
        WHERE PersonID BETWEEN StartID
                AND EndID
            AND GroupID IN (
                SELECT LabelValue
                FROM LabelTable
                WHERE UPPER(LabelName) LIKE '#DELETE#%'
                )
        );
 
-- Delete Person from PersonTable if in the Group
DELETE
FROM PersonTable
WHERE PersonID IN (
        SELECT PersonID
        FROM PersonTable
            ,GroupTable
        WHERE PersonID BETWEEN StartID
                AND EndID
            AND GroupID IN (
                SELECT LabelValue
                FROM LabelTable
                WHERE UPPER(LabelName) LIKE '#DELETE#%'
                )
        );