I put this in 'off-topic' as strictly speaking it's not LC but looking for help with SQLite...
I have an SQLite database with a table "events" with a columns events.eventDate, events.ageAtEvent and events.foreignKey_participants, and a table "participants" with columns participants.primaryKey and participants.dob.
The eventDate and date of birth (dob) are in SQL format yyyy-mm-dd - the age calculation included below works admirably on single records.
However, I want to update all rows in events.ageAtEvent with current age at the time of event for each participant.
What I've tried:
Code: Select all
UPDATE events
SET ageAtEvent = (
SELECT (strftime('%Y', events.eventDate) - strftime('%Y', participants.dob)) - (strftime('%m-%d', events.eventDate) < strftime('%m-%d', participants.dob))
FROM events JOIN participants ON events.foreignKey_participants = participants.primaryKey
);
Very grateful for advice...
Stam