Logo Home    Page list

Event - Add Marriage to Couples Without #events

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.

Event-Add_Marriage.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;
www.000webhost.com