Simple SQL question?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Simple SQL question?

Bart Smissaert
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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" <[hidden email] on behalf of [hidden email]> 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
    [hidden email]
    http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <
[hidden email]> 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" <
> [hidden email] on behalf of
> [hidden email]> 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
>     [hidden email]
>     http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple SQL question?

David Raymond
In reply to this post by Bart Smissaert
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple SQL question?

Keith Medcalf
In reply to this post by Bart Smissaert

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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple SQL question?

Bart Smissaert
In reply to this post by David Raymond
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 <[hidden email]>
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:[hidden email]]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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-
>[hidden email]] 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
><[hidden email]>
>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-
>[hidden email]]
>> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple SQL question?

petern
In reply to this post by Bart Smissaert
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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple SQL question?

Bart Smissaert
In reply to this post by Keith Medcalf
They end up in the wrong row.

RBS

On Wed, Nov 15, 2017 at 9:59 PM, Keith Medcalf <[hidden email]> 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-
> >[hidden email]] 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
> ><[hidden email]>
> >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-
> >[hidden email]]
> >> 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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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-
>[hidden email]] 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 <[hidden email]>
>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-
>> >[hidden email]] 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
>> ><[hidden email]>
>> >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-
>> >[hidden email]]
>> >> 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
>> >> [hidden email]
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> >users
>> >> _______________________________________________
>> >> sqlite-users mailing list
>> >> [hidden email]
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> >users
>> >>
>> >_______________________________________________
>> >sqlite-users mailing list
>> >[hidden email]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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-
> >[hidden email]] 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 <[hidden email]>
> >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-
> >> >[hidden email]] 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
> >> ><[hidden email]>
> >> >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-
> >> >[hidden email]]
> >> >> 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
> >> >> [hidden email]
> >> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >> >users
> >> >> _______________________________________________
> >> >> sqlite-users mailing list
> >> >> [hidden email]
> >> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >> >users
> >> >>
> >> >_______________________________________________
> >> >sqlite-users mailing list
> >> >[hidden email]
> >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users