It is often helpful to study the syntax diagrams to see what is possible and intended by the language:
https://sqlite.org/lang_update.html Take a look at the WHERE clause. The WHERE clause determines which rows are UPDATEd. One weakness in the documentation (although it may otherwise generally be known about SQL) is that one may fully qualify column names with table dot prefix. In this case, you can precisely constrain TABLE_A.ID in the WHERE clause of the UPDATE according to your requirements. [FYI: there may be a typo in the suggested answer I saw posted earlier with a WHERE clause. If you want to affect rows of the UPDATEd table, the WHERE clause has to be at the scope of the UPDATE statement, not a subquery.] On Wed, Nov 15, 2017 at 1:16 PM, Bart Smissaert <[email protected]> wrote: > 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 > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
