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 '' ) ;