Update Qry to change AKA to Marriage
Hi
I am new to RM/RM8 an SQLite (Personal).
I want to update ALL females that will update any AKA name type to Marriage name type. I believe AKA =1 and MARRIAGE =5. I do not want to modify the "primary name". Will the code below accomplished that, do I need anything else? as a side note I am going to ask to do a second step to fix the name. The reason these need to be fixed is because my old software did not handle them correctly.
UPDATE NameTable
SET NameType = 5
LEFT JOIN PersonTable ON (OwnerID = PersonID)
WHERE NOT IsPrimary AND NameType = 1 and Sex = 1
That looks okay. Of course you will make sure you have a safe copy of your database file in case something goes wrong.
You might find this relevant: Names - Add Married. If you do try it with RM8, let us know if it works okay - not yet been tested.
Thanks! Can you point me to where I can learn what "RMNOCASE" is and its impacts on RM?
I tried the SQL but got the following error:
Execution finished with errors.
Result: table NameTable has 23 columns but 18 values were supplied
At line 31:
INSERT OR REPLACE INTO NameTable
SELECT
NULL AS NameID,
Wife.OwnerID AS OwnerID,
Husband.Surname AS Surname,
Husband.Given AS Given,
'Mrs.' AS Prefix,
'(' || Wife.Surname || ', ' || Wife.Given || ')' AS Suffix,
Wife.Nickname AS Nickname,
5 AS NameType,
Event.Date AS Date, -- set to ',' for undated, Event.Date to match Marriage date.
CASE Event.SortDate & 1023
WHEN 1023 THEN Event.SortDate --
ELSE Event.SortDate + 1
END AS SortDate,
-- Event.SortDate +1 AS SortDate, -- set to 9223372036854775807 to sort with undated Alt Names, Event.SortDate to match Marriage
0 AS IsPrimary,
0 AS IsPrivate, -- set to 1 to make Private
0 AS Proof,
0.0 AS EditDate,
-- 'After [person:hisher] marriage, [person] was also known as [Desc].' AS Sentence,
' ' AS Sentence, -- a blank space to prevent the default sentence from being outputted
'' AS Note,
Wife.BirthYear AS BirthYear,
Wife.DeathYear AS DeathYear
FROM NameTable Wife
INNER JOIN FamilyTable Family ON Wife.OwnerID=MotherID AND +Wife.IsPrimary
INNER JOIN NameTable Husband ON FatherID = Husband.OwnerID AND +Husband.IsPrimary
INNER JOIN EventTable Event ON Family.FamilyID = Event.OwnerID AND Event.EventType = 300
INNER JOIN PersonTable Person2 ON Wife.OwnerID = Person2.PersonID AND Person2.Sex=1
INNER JOIN PersonTable Person1 ON Husband.OwnerID = Person1.PersonID AND Person1.Sex=0
WHERE
Wife.Surname NOT LIKE Husband.Surname AND
Wife.OwnerID NOT IN
(
-- Wives with Alternate Surnames matching Husband's Surname
SELECT Wife.OwnerID --, Wife.IsPrimary, Wife.Given, Wife.Surname, Husband.Surname
FROM NameTable Wife
INNER JOIN FamilyTable ON Wife.OwnerID=MotherID AND NOT +Wife.IsPrimary
INNER JOIN NameTable Husband ON FatherID = Husband.OwnerID AND +Husband.IsPrimary
WHERE Wife.Surname LIKE Husband.Surname
OR Wife.Surname LIKE Wife.Surname || '-' || Husband.Surname
)
;
if someone has a suggestion how to fix this to work with RM8 I would appreciate it