Update Query for Buiral events < Deathdate
I would like a query to update burial events for person when both are present and when BurialDate < DeathDate --- the result should update the Burial Event to "AFT" Death Date (DA. deathdate)
using this logic basically
Update
SET Burial Date= AFT Death Date
WHERE
(burialdatetr < deathdatetr)
and
(burialdatetr <> 0 and deathdatetr <> 0)
I have about 400 events that have burial events that are year only. I do not want update anything where burial date is after death date -- which means it is correct
the weirdness of the date field add a layer of complexity -- hope someone with update query skills can assist
I would create a temporary table or view to filter for those Burial events needing an Update. Maybe something along these lines:
SELECT
E2.EventID AS BurialEventID
,E1.EventID AS DeathEventID
,E1.OwnerID
,E1.Date AS DeathDate
,E2.Date AS BurialDate
FROM EventTable E1
JOIN EventTable E2
USING(OwnerID)
WHERE
E1.EventType=2
AND E1.Date NOT LIKE '.%'
AND E2.EventType=4
AND SUBSTR(E2.Date,8,4)<SUBSTR(E1.Date,8,4)
;
Once you've filtered down to the set you want to change, then you might build another that uses it to generate the desired Date value and UPDATE from it.
I revised that query because it does not preclude text dates (invalid dates) for which the 1st character is T. That would be simple enough to add as another constraint but as we've been discussing regular expressions in another thread, I thought it might be opportune to use REGEXP instead by replacing the line:
AND E1.Date NOT LIKE '.%'
with
AND E1.Date REGEXP '^.{3}[0-9]{8}.+'
which looks for an 8 character string of digits starting at the 4th position in Date. While it is possible to enter an "invalid date" value in the Date field that would have such a pattern, it seems highly unlikely as it would be nonsensical. While the addition of "AND E1.Date NOT LIKE 'T%'" to either of the above would rule out all text dates, I bring up the REGEXP method because I got unexpected results (nil) trying it in SQLite Studio loaded with the re.dll extension from sqlean and had to switch to SQLiteSpy. It looks like sqlean has only a rudimentary subset of the POSIX regular expression syntax.
@kevync, one thing you've overlooked in your OP and I have, too, until now is that you are undoubtedly going to want the SortDate field to reflect the revised Date field. That adds a whole other level of complexity to the process. It might be addressed simply by running the Dates - Same Day Sort Order script after updating the Burial Dates.
As it's now 4 days since your post, in that period of time you could well have completed the manual updating of the Burial dates. My initial query could well help along in that process as it shows the RIN of each person having a Burial date on the same day as or before their Death or not at all. Maybe the RM Problem List also shows them.
Thanks TOM! this looks like what I was looking for and will play with it.
What is the difference between using ON vs USING? (never used USING before).
I was going to worry about later and spent time with other projects vs spending 1/2 day manually updating over 300 records -- plus if I started reviewing I would notice other things that needed to be fixed and that would turn into days 😉
So this is how I modified the SQL to get the end result I was looking for. I want to ignore Buiral dates that are "null"/blank. I will work with this to create a temp table (BurialEventID / OwnerID / BurialDate) then update event table
SELECT
E2.EventID AS BurialEventID
,E1.EventID AS DeathEventID
,E1.OwnerID
,E1.Date AS DeathDate
,E2.Date AS BurialDate
FROM EventTable E1
JOIN EventTable E2
USING(OwnerID)
WHERE
E1.EventType=2
AND E1.Date REGEXP '^.{3}[0-9]{8}.+'
AND E2.EventType=4
AND E2.Date <> '.'
AND SUBSTR(E2.Date,8,4)<SUBSTR(E1.Date,8,4)
Why exclude those people who have a Burial event with no date?
AND E2.Date <> '.'
They will sort to the beginning of the events unless you override with the Sort Date.
This version adds NewBurialDate column. It is for only the exact death date format beginning "D.". That could be expanded to cover other modifiers but I'm unsure how "After" modifier in Burial will relate to other modifiers for Death. The full list of known modifiers is explained in the Date sheet within RootsMagic4DataDefs.ods.
SELECT
E2.EventID AS BurialEventID
,E1.EventID AS DeathEventID
,E1.OwnerID
,E1.Date AS DeathDate
,E2.Date AS BurialDate
,REPLACE(E1.Date,'D.','DA') AS NewBurialDate
FROM EventTable E1
JOIN EventTable E2
USING(OwnerID)
WHERE
E1.EventType=2
AND E1.Date REGEXP '^[^\.T].{2}[0-2][0-9]{3}[0-1][0-2][0-3][0-9].+'
AND E2.EventType=4
AND SUBSTR(E2.Date,8,4)<SUBSTR(E1.Date,8,4)
;
The convoluted REGEXP is just testing SQLiteSpy's syntax and trying to filter for a more date-like value. Really overkill if you trust that all Date values beginning with "D" are valid dates in which case it could be replaced by
AND E1.Date LIKE 'D%'
I'm late to this thread and I don't have any technical SQLite advice to offer that would be any different than what Tom has already suggested. I would only emphasize the complexity of dealing with both the date field and the sort date field from SQLite. Instead, let me offer a different perspective on the whole idea of burial dates.
I used to enter burial dates in the same or very similar manner to that which you describe. Essentially, I entered the actual burial date as the burial date if I knew it, and I entered the burial date as "after death date" if I didn't know it. But somewhere along the way, I decided that doing it that way isn't really accurate. About the only way I ever knew an actual burial date was from a death certificate or from an obituary. It seems to me that any other burial date really isn't accurate.
Here are a couple of examples. If all I have is a burial marker with 1823-1890, I don't think it's correct to say the person was buried "after 1890" because there is a high probability that they actually were buried in 1890. And even if I have a burial marker with 12 Feb 1823 - 10 Jun 1890, I don't think it's necessarily accurate to say they were buried "after 10 Jun 1890" because the might well have been buried the same day they died. Plus, saying "after 10 Jun 1890" for the burial date is not really adding any new information, even if it is correct. And it's suggesting that you know they weren't buried on the same day they died, even if you don't know that for sure.
So I now leave the burial date blank unless I know what it is. The burial fact will default to being physically after all the facts that do have dates. And in the cases I might have multiple facts after the death fact such as for probates and settlements, I use a sort date on the burial fact to be sure it's after the death fact and before facts such as probate and settlement.
Finally, I live the land of the busy beavers of the Tennessee Valley Authority which for many years were building dams that flooded many cemeteries and therefore they were reinterring many burials. So I have lots of people who are now buried in a different cemetery than where they were originally interred, and I have two different burial dates to take into account. I suspect that most genealogists don't have to take such things into account.
I can only remember seeing on person in my tree where burial was apparently moved -- I do have a small handful where they have headstones in more than one location (Something more common I would think)
if I have no burial info -- I leave blank. However, as you did I generally did Aft DOD (After 10 Sep 2022) for burial when I knew DOD but not actual burial date and had location or cemetery info. Also, I only used AFT when full DOD was known. (and burial was not yet known)
Often the burial date is indicated on Death Certificate but not always depending on time period and location. In more modern times the burial time could be much later than death, especially locations with frozen ground.
I use a sort date on the burial fact to be sure it's after the death fact and before facts such as probate and settlement.
this is something I will have to explore more with.
Thanks for giving me some additional thing to think about. Not I may instead update any "DA" burials to Null/blank.
Kevin
update:
Decided to go with blank burial dates for unknowns ... that changed over 2100 events to '.' vs the 'DA.' . Since they already had the sort date it -- until I update them they will sort after the death date in most cases.
The only other situation I have seen is when people may be living (or were when stone was last updated) and the date on stone might say (1930- ) at time photo was taken -- usually on Find-a-Grave. Usually its cheaper that way to update later.