LiveCode Forums

Posted: **Tue May 30, 2023 8:01 pm**

Hi folks,
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
);

This populates all rows with the same ageAtEvent, presumably the first one. I'm not clear how apply the correct calculation to each row.
Very grateful for advice...

Stam

Posted: **Thu Jun 01, 2023 6:50 pm**

The answer was simple and a bit strange, but hey it works...

Code: Select all

UPDATE events
SET ageAtEvent = (strftime('%Y', eventDate) - strftime('%Y', participants.dob)) - (strftime('%m-%d', eventDate) < strftime('%m-%d', participants.dob))
FROM participants 
WHERE events.foreignKey_participants = participants.primaryKey;

LiveCode Forums

Looking for SQLite advice -- update each row with a calculation from a join

Looking for SQLite advice -- update each row with a calculation from a join

Re: Looking for SQLite advice -- update each row with a calculation from a join