Re: [sqlite] Simple SQL question?

[Bart Smissaert]

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 <kmedc...@dessus.com> 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-
> >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)
&g

Re: [sqlite] Simple SQL question?

[Keith Medcalf]

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

Re: [sqlite] Simple SQL question?

[Bart Smissaert]

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

Re: [sqlite] Simple SQL question?

[petern]

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 
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
> 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

Re: [sqlite] Simple SQL question?

[Keith Medcalf]

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

Re: [sqlite] Simple SQL question?

[Bart Smissaert]

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 
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

Re: [sqlite] Simple SQL question?

[Keith Medcalf]

UPDATE table_a
   SET issue_date = (SELECT GetAgeAtDate(dob, issue_date)
   FROM table_p
  WHERE table_p.id = id);

---
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:17
>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

Re: [sqlite] Simple SQL question?

[David Raymond]

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

Re: [sqlite] Simple SQL question?

[Bart Smissaert]

I like to just update that table as this AGE_AT_ISSUE column will be used
often in various statements.
There must be a simple way to do this, but just can't figure it out.

RBS


On Wed, Nov 15, 2017 at 9:25 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Wouldn’t you create a view instead, and not bother calculating
> age_at_issue until necessary since it’s derived completely from two other
> columns?
>
> On 11/15/17, 3:16 PM, "sqlite-users on behalf of Bart Smissaert" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> bart.smissa...@gmail.com> 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
> 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

Re: [sqlite] Simple SQL question?

[Peter Da Silva]

Wouldn’t you create a view instead, and not bother calculating age_at_issue 
until necessary since it’s derived completely from two other columns?

On 11/15/17, 3:16 PM, "sqlite-users on behalf of Bart Smissaert" 
 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
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

Re: [sqlite] Simple SQL question?

[Bart Smissaert]

Tried your SQL, but it doesn't run.
Will fiddle it and see if I can make it work.

RBS

On Wed, Nov 17, 2010 at 9:00 AM, luuk34  wrote:
> On 17-11-10 09:58, Bart Smissaert wrote:
>> What do you suggest should be the full SQL then?
>>
> select  t1.patient_id
> from    table1 t1
> join    (
>             select      table1.address,
>                         min( table1.date_of_birth ) as date_of_birth
>             from        table1
>             group by    table1.address
>         )
> as      t2
> ON     t2.address = t1.address
> and     t2.date_of_birth = t1.date_of_birth
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[luuk34]

On 17-11-10 10:00, luuk34 wrote:
> On 17-11-10 09:58, Bart Smissaert wrote:
>> What do you suggest should be the full SQL then?
>>
> select  t1.patient_id
> fromtable1 t1
> join(
> select  table1.address,
> min( table1.date_of_birth ) as date_of_birth
> fromtable1
> group bytable1.address
> )
> as  t2
> ON t2.address = t1.address
> and t2.date_of_birth = t1.date_of_birth
>
i forgot the link:
http://www.sqlite.org/syntaxdiagrams.html#join-constraint
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[luuk34]

On 17-11-10 09:58, Bart Smissaert wrote:
> What do you suggest should be the full SQL then?
>
select  t1.patient_id
fromtable1 t1
join(
 select  table1.address,
 min( table1.date_of_birth ) as date_of_birth
 fromtable1
 group bytable1.address
 )
as  t2
ON t2.address = t1.address
and t2.date_of_birth = t1.date_of_birth

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[Bart Smissaert]

What do you suggest should be the full SQL then?

RBS

On Wed, Nov 17, 2010 at 8:16 AM, luuk34  wrote:
> On 17-11-10 00:17, Petite Abeille wrote:
>> select  t1.patient_id
>> from    table1 t1
>> join    (
>>              select      table1.address,
>>                          min( table1.date_of_birth ) as date_of_birth
>>              from        table1
>>              group by    table1.address
>>          )
>> as      t2
>> join    t2.address = t1.address
>
> 'join' should be:
>
> ON    t2.address = t1.address
>
>
>> and     t2.date_of_birth = t1.date_of_birth
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[Bart Smissaert]

Tried your SQL, but it doesn't look right and didn't run. Will see if
I can alter it.

RBS


On Tue, Nov 16, 2010 at 11:17 PM, Petite Abeille
 wrote:
>
> On Nov 16, 2010, at 11:55 PM, Bart Smissaert wrote:
>
>> This seems to work fine,
>
> Then you are golden :)
>
>> but I am not sure if this SQL is correct and
>> if the results will always be correct and have a feeling
>> that there must be a better construction.
>
>
>
>> Any suggestions?
>
> Nothing very meaningful, but you could rewrite the 'in' clause as a 'join' to 
> avoid all these concatenations, e.g.:
>
> select  t1.patient_id
> from    table1 t1
> join    (
>            select      table1.address,
>                        min( table1.date_of_birth ) as date_of_birth
>            from        table1
>            group by    table1.address
>        )
> as      t2
> join    t2.address = t1.address
> and     t2.date_of_birth = t1.date_of_birth
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[Bart Smissaert]

That is a strange construction and for now I haven't got it to work
yet in my VB application.
It does run though in Firefox SQLite manager. Maybe after all the SQL
I came up with in the end wasn't that bad.

RBS


On Wed, Nov 17, 2010 at 12:09 AM, Igor Tandetnik  wrote:
> Bart Smissaert  wrote:
>> Have (simplified) a table like this:
>>
>> CREATE TABLE TABLE1(
>>                         [PATIENT_ID] INTEGER PRIMARY KEY,
>>                         [ADDRESS] TEXT,
>>                         [DATE_OF_BIRTH] TEXT)
>>
>> DATE_OF_BIRTH is in the ISO8601 format -mm-dd
>>
>> Now I need a SQL to find the oldest patients living at all the
>> different (unique) addresses, so this will be
>> the patient with the lowest DATE_OF_BIRTH. I will need the PATIENT_ID
>> of that patient and nil else.
>
> select (select PATIENT_ID from TABLE1 t1
>            where t1.ADDRESS = t2.ADDRESS
>            order by DATE_OF_BIRTH limit 1)
> from (select distinct ADDRESS from TABLE1) t2;
>
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[luuk34]

On 17-11-10 00:17, Petite Abeille wrote:
> select  t1.patient_id
> fromtable1 t1
> join(
>  select  table1.address,
>  min( table1.date_of_birth ) as date_of_birth
>  fromtable1
>  group bytable1.address
>  )
> as  t2
> joint2.address = t1.address

'join' should be:

ONt2.address = t1.address


> and t2.date_of_birth = t1.date_of_birth

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[Igor Tandetnik]

Bart Smissaert  wrote:
> Have (simplified) a table like this:
> 
> CREATE TABLE TABLE1(
> [PATIENT_ID] INTEGER PRIMARY KEY,
> [ADDRESS] TEXT,
> [DATE_OF_BIRTH] TEXT)
> 
> DATE_OF_BIRTH is in the ISO8601 format -mm-dd
> 
> Now I need a SQL to find the oldest patients living at all the
> different (unique) addresses, so this will be
> the patient with the lowest DATE_OF_BIRTH. I will need the PATIENT_ID
> of that patient and nil else.

select (select PATIENT_ID from TABLE1 t1
where t1.ADDRESS = t2.ADDRESS
order by DATE_OF_BIRTH limit 1)
from (select distinct ADDRESS from TABLE1) t2;

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQL question?

[Petite Abeille]

On Nov 16, 2010, at 11:55 PM, Bart Smissaert wrote:

> This seems to work fine,

Then you are golden :)

> but I am not sure if this SQL is correct and
> if the results will always be correct and have a feeling
> that there must be a better construction.



> Any suggestions?

Nothing very meaningful, but you could rewrite the 'in' clause as a 'join' to 
avoid all these concatenations, e.g.:

select  t1.patient_id
fromtable1 t1
join(
select  table1.address,
min( table1.date_of_birth ) as date_of_birth
fromtable1
group bytable1.address
) 
as  t2
joint2.address = t1.address
and t2.date_of_birth = t1.date_of_birth


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users