Names – Move Prefix to empty Suffix #names

This little query responds to the desire expressed by RootsMagic Forums member Ksquared to relocate Titles of Nobility from Prefix to Suffix. This is where they ended up in the direct import of his TMG project to RootsMagic. It moves the prefix for each name to the suffix field, provided it is empty. If the suffix field is non-empty, the prefix is left in situ.

The query require a SQLite manager having the RMNOCASE collation, such as SQLiteSpy or SQLite Expert Personal. See SQLite Managers for download links.

Having opened the RootsMagic database with the SQLite manager, copy the statement below into the SQL editor and execute it.

After executing, use the RootsMagic menu File > Database Tools to check database integrity, if not OK then Rebuild Indexes and repeat.

UPDATE NameTable
SET Suffix = Prefix
    , Prefix = ''
WHERE
Prefix NOT LIKE ''  -- only those records with both non-empty Prefix
AND NameID          -- AND empty suffix
NOT IN
(
  SELECT NameID     -- records having a non-empty suffix
  FROM NameTable
  WHERE
  Suffix NOT LIKE ''
)
;