Delete+Many

toc 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 yet to be identified.

Two SQLite procedures have been developed as a workaround without the losses attendant to partial export and drag and drop: 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#".
 * 1) Delete by Color Coding
 * 2) Delete by Named Group

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:
 * 3) Rebuild Indexes
 * 4) Compact database
 * 5) RootsMagic > Tools > Count Trees. Inspect and deal with any undesired results.

=Delete by Color Coding= code format="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;

code

=Delete by Named Group= code format="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#%' )		); code