That is getting close, but the calculated values end up with the right ID, but the wrong ISSUE_DATE. Will if an order by can sort this out.
RBS On Wed, Nov 15, 2017 at 9:33 PM, David Raymond <david.raym...@tomtom.com> wrote: > Try... > > UPDATE TABLE_A SET AGE_AT_ISSUE = > (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM > TABLE_P AS P WHERE P.ID = ID); > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Wednesday, November 15, 2017 4:17 PM > To: General Discussion of SQLite Database > Subject: [sqlite] Simple SQL question? > > Have 2 tables, TABLE_A and TABLE_P like this: > > CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER) > > CREATE TABLE_P(ID INTEGER, DOB INTEGER) > > ID is he common field. > > Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the > age of the person identified by ID, at the date of ISSUE_DATE. > I do this with a UDF, taking 2 arguments, in this case DOB (date of birth) > and ISSUE_DATE. > The UDF is not relevant in this example and it could as well be ISSUE_DATE > - DOB. > > I tried this: > > UPDATE TABLE_A SET AGE_AT_ISSUE = > (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A > INNER JOIN TABLE_P P ON(A.ID = P.ID) > > But that will make the column AGE_AT_ISSUE have the same value for all > rows, which is he first row of the select. > > The select by itself will give the right values. > > Any suggestions how this can be done with just one statement? > > > RBS > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
