Script help #placedetails
I'm still a novice with SQLite scripts. Is there a good template you can recommend I start from that would enable me to delete any place detail (leaving place intact) containing "Evangelisch" from every individual's Birth fact?
I used the Birth-AddFromChristenBaptism.sql script to create missing birth facts from Christening events but the Christening events often include "Evangelisch" in the place details field and that was copied over to the new birth facts. Obviously, all of these individuals were not born in a church!
That's a novel situation that hasn't been directly addressed here before. But it is one that can be dealt with in SQLite with an understanding of the database structure. I'm on a phone so I can only answer generally.
In the EventTable, the EventType relates to the FactTypeID in the FactTypeTable. Look up the ID for Birth in the latter (I think it's 1). So you can filter the EventTable for Birth facts using the condition WHERE EventType=1.
I'll have to continue this another time on a bigger screen.
The second piece of this is those Place Details containing "Evangelisch". Place Details are stored in the PlaceTable with PlaceType=2 and are 'owned' by the PlaceID matching their MasterID. So this simple query will return all the PlaceIDs for the Place Details of possible interest in a temporary VIEW that we can inspect and reuse.
CREATE TEMP VIEW Sites AS
SELECT PlaceID FROM PlaceTable
WHERE PlaceType = 2 -- Place Detail
AND Name LIKE '%Evangelisch%' -- % is the wildcard like * is in other systems
;
In the EventTable, the event's Place Detail is filled by retrieving the PlaceTable record matching SiteID. So we can find and reuse the EventIDs of all the Births that have the unwanted Place Detail:
CREATE TEMP VIEW BirthsWithSite AS
SELECT EventID FROM EventTable
WHERE EventType=1
AND SiteID IN (SELECT * FROM Sites)
;
Now we can reset these Births' SiteIDs to 0:
UPDATE EventTable SET SiteID=0
WHERE EventID IN (SELECT * FROM BirthsWithSites)
;
I haven't tested this but it should work. Of course, you'll use it on a copy of your database file!!