update Sortdate for Burial
I created a Temp table (TempFixBurialDates)--- it contains the col (BurialDate) need to update the EventTable. The Sql runs but does not have the desired result. I have limited experience with update / subquery. The TempTable also has EventID, PersonID (OwnerID).
SET SortDate =
(
SELECT BurialDate
FROM TempFixBurialDates
WHERE EventID = BurialEventID
)
It's not going to work for a couple of reasons. One is that the formats respectively stored for Event dates and their corresponding Sort dates are totally different. The former is a structured text field; the latter is an integer. For example:
EventTable.Date="D.+19200101..+00000000.."
EventTable.SortDate=6710399196088762380
The conversions from UI displayed date and the stored formats are quite complex. Search on the #date tag for some references.
You also must constrain your UPDATE so that only those rows having an EventID matching your BurialEventID are affected; otherwise, every other EventID will have its Date field overwritten by a null result from the SET clause. Something like this is needed:
UPDATE EventTable
SET SortDate =
(...
)
WHERE EventID IN (SELECT BurialEventID FROM TempFixBurialDates)
Thanks TOM!
Where IN (EventID) was the missing part I think as far as the query.
I knew I only wanted to update (constrain) but needed to get the syntax to do so.
The binary sort will be bit more complicated. I can identify them and add the people to work out of group. There is were being able to work/view/edit the table (like Family Historian allows) would be helpful
Kevin