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-
>boun...@mailinglists.sqlite.org] 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 <kmedc...@dessus.com>
>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-
>> >boun...@mailinglists.sqlite.org] 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
>> ><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
>>
>>
>>
>> _______________________________________________
>> 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
