Thanks, they both work indeed.
Nice work!
This bit looks strange to me:
SELECT GetAgeAtDate(dob, issue_date)
FROM table_p
as issue_date is not in table_p.
Never realised that this could work and learned something there.
RBS
On Wed, Nov 15, 2017 at 10:12 PM, Keith Medcalf <[email protected]> wrote:
>
> Then try making the where clause explicitly qualified with the table names.
>
> UPDATE table_a
> SET issue_date = (SELECT GetAgeAtDate(dob, issue_date)
> FROM table_p
> WHERE table_p.id = table_a.id);
>
> Actually, that would be correct. You can use the query of the form
>
> UPDATE table_a
> SET issue_date = (SELECT GetAgeAtDate(dob, issue_date)
> FROM table_p
> WHERE id = table_a.id);
>
> as unqualified duplicate named columns in the correlated subquery are
> preferentially assumed to refer to tables mentioned in the from clause of
> the correlated subquery.
>
> Duplicated column names that you wish to refer to the outer correlated
> table must be qualified. Non-duplicate column names will be found where
> they exist, either in the inner or outer correlate.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[email protected]] On Behalf Of Bart Smissaert
> >Sent: Wednesday, 15 November, 2017 15:05
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Simple SQL question?
> >
> >They end up in the wrong row.
> >
> >RBS
> >
> >On Wed, Nov 15, 2017 at 9:59 PM, Keith Medcalf <[email protected]>
> >wrote:
> >
> >>
> >> That is not possible since there is only one column called
> >issue_date in
> >> all the tables mentioned in the query ...
> >>
> >>
> >> ---
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >> >-----Original Message-----
> >> >From: sqlite-users [mailto:sqlite-users-
> >> >[email protected]] On Behalf Of Bart Smissaert
> >> >Sent: Wednesday, 15 November, 2017 14:55
> >> >To: SQLite mailing list
> >> >Subject: Re: [sqlite] Simple SQL question?
> >> >
> >> >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
> >> ><[email protected]>
> >> >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-
> >> >[email protected]]
> >> >> 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
> >> >> [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
> >> >>
> >> >_______________________________________________
> >> >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
> >>
> >_______________________________________________
> >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
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
