Event+-+Add+Marriage+to+Couples+Without

RootsMagic recommends that a Marriage fact be added to all couples if it is believed that they were married, even if there is neither proof nor knowledge of the date and place. It might also be said that the Marriage fact could be used to define all manner of unions. This query simply adds a Marriage event to any couple (family) in a database that does not have this fact already. All fields are left blank except a private note is included: "{generated by SQLite query}" to aid in finding them using RootsMagic.

code format="sql" /* Event-Add_Marriage.sql 2013-01-16 Tom Holden ve3meo

Adds a Marriage event to all couples lacking one. No date, place or description. A private note is added.

BEGIN TRANSACTION;

INSERT INTO EventTable SELECT NULL AS EventID ,300 AS EventType ,1 AS OwnerType ,FamilyID AS OwnerID ,0 AS FamilyID ,0 AS PlaceID ,0 AS SiteID ,'.' AS DATE ,9223372036854775807 AS SortDate ,0 AS IsPrimary ,0 AS IsPrivate ,0 AS Proof ,0 AS STATUS ,0 AS EditDate ,CAST('' AS BLOB) AS Sentence ,CAST('' AS BLOB) AS Details ,CAST('{generated by SQLite query}' AS BLOB) AS Note FROM FamilyTable WHERE FamilyID NOT IN (		-- Couples with Marriage Fact		SELECT F.FamilyID		FROM FamilyTable F		INNER JOIN EventTable E ON F.FamilyID = E.OwnerID			AND E.OwnerType = 1			AND E.EventType = 300		);

COMMIT TRANSACTION; code