syntax error near AS

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

syntax error near AS

John-375
Hi

Wondering if someone else can spot the syntax error in the following
statement. "locns" is an attached database. There are four "AS" terms in
the statement, they all alias tables.

Ok, found it. It seems that an alias for an "UPDATE" table name is not
permitted. Is there a particular reason for this?
I would think it a convenience especially when using long table names
and attached databases.

John

sqlite>     UPDATE locns.xxx_last_delivery AS tgt
   ... >      SET
   ... >         tgt.del_date =  (
   ... >              SELECT src.last_del_d
   ... >              FROM   main.updates AS src
   ... >              WHERE  src.custnum = tgt.custnum),
   ... >         tgt.del_qty = (
   ... >              SELECT src.last_del_q
   ... >              FROM   main.updates AS src
   ... >              WHERE  src.custnum = tgt.custnum)
   ... >      WHERE
   ... >         tgt.custnum  = (
   ... >              SELECT src.custnum
   ... >              FROM   main.updates AS src
   ... >              WHERE  src.last_del_d IS NOT NULL
   ... >              AND    src.last_del_d > tgt.del_date)
   ... >  ;
Error: near "AS": syntax error

--
Regards
    John McMahon
       [hidden email]


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

Re: syntax error near AS

Keith Medcalf

Do you know of any implementation of SQL that accepts an AS clause for the updated table?  I don't think any do.

Some versions have a FROM extension and you CAN specify an alias for the updated table in that clause, however, as far as I know the update table cannot be aliased and the "set <column> = ..." the <column> must always be a column in the updated table and while you may be allowed to "adorn" it in some implementations, any adornments are ignored (or trigger an error message if they are not the same as the updated table).

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of John McMahon
> Sent: Wednesday, 5 July, 2017 21:17
> To: SQLite Users
> Subject: [sqlite] syntax error near AS
>
> Hi
>
> Wondering if someone else can spot the syntax error in the following
> statement. "locns" is an attached database. There are four "AS" terms in
> the statement, they all alias tables.
>
> Ok, found it. It seems that an alias for an "UPDATE" table name is not
> permitted. Is there a particular reason for this?
> I would think it a convenience especially when using long table names
> and attached databases.
>
> John
>
> sqlite>     UPDATE locns.xxx_last_delivery AS tgt
>    ... >      SET
>    ... >         tgt.del_date =  (
>    ... >              SELECT src.last_del_d
>    ... >              FROM   main.updates AS src
>    ... >              WHERE  src.custnum = tgt.custnum),
>    ... >         tgt.del_qty = (
>    ... >              SELECT src.last_del_q
>    ... >              FROM   main.updates AS src
>    ... >              WHERE  src.custnum = tgt.custnum)
>    ... >      WHERE
>    ... >         tgt.custnum  = (
>    ... >              SELECT src.custnum
>    ... >              FROM   main.updates AS src
>    ... >              WHERE  src.last_del_d IS NOT NULL
>    ... >              AND    src.last_del_d > tgt.del_date)
>    ... >  ;
> Error: near "AS": syntax error
>
> --
> Regards
>     John McMahon
>        [hidden email]
>
>
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: syntax error near AS

Paul Sanderson
The SQLite syntax diagrams are my first point of call when looking at an
error in my code like this.

https://sqlite.org/lang_update.html

"AS" and an alias are clearly not part of the statement.


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 6 July 2017 at 06:03, Keith Medcalf <[hidden email]> wrote:

>
> Do you know of any implementation of SQL that accepts an AS clause for the
> updated table?  I don't think any do.
>
> Some versions have a FROM extension and you CAN specify an alias for the
> updated table in that clause, however, as far as I know the update table
> cannot be aliased and the "set <column> = ..." the <column> must always be
> a column in the updated table and while you may be allowed to "adorn" it in
> some implementations, any adornments are ignored (or trigger an error
> message if they are not the same as the updated table).
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
> > -----Original Message-----
> > From: sqlite-users [mailto:[hidden email]]
> > On Behalf Of John McMahon
> > Sent: Wednesday, 5 July, 2017 21:17
> > To: SQLite Users
> > Subject: [sqlite] syntax error near AS
> >
> > Hi
> >
> > Wondering if someone else can spot the syntax error in the following
> > statement. "locns" is an attached database. There are four "AS" terms in
> > the statement, they all alias tables.
> >
> > Ok, found it. It seems that an alias for an "UPDATE" table name is not
> > permitted. Is there a particular reason for this?
> > I would think it a convenience especially when using long table names
> > and attached databases.
> >
> > John
> >
> > sqlite>     UPDATE locns.xxx_last_delivery AS tgt
> >    ... >      SET
> >    ... >         tgt.del_date =  (
> >    ... >              SELECT src.last_del_d
> >    ... >              FROM   main.updates AS src
> >    ... >              WHERE  src.custnum = tgt.custnum),
> >    ... >         tgt.del_qty = (
> >    ... >              SELECT src.last_del_q
> >    ... >              FROM   main.updates AS src
> >    ... >              WHERE  src.custnum = tgt.custnum)
> >    ... >      WHERE
> >    ... >         tgt.custnum  = (
> >    ... >              SELECT src.custnum
> >    ... >              FROM   main.updates AS src
> >    ... >              WHERE  src.last_del_d IS NOT NULL
> >    ... >              AND    src.last_del_d > tgt.del_date)
> >    ... >  ;
> > Error: near "AS": syntax error
> >
> > --
> > Regards
> >     John McMahon
> >        [hidden email]
> >
> >
> > _______________________________________________
> > 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
|  
Report Content as Inappropriate

Re: syntax error near AS

Clemens Ladisch
In reply to this post by John-375
John McMahon wrote:
> an alias for an "UPDATE" table name is not permitted. Is there a particular reason for this?

The UPDATE statement affects a single table.  While an alias might be
a convenience, it is not necessary (any naming conflicts in subqueries
can be resolved by using an alias on the table(s) used there).


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

Re: syntax error near AS

Domingo Alvarez Duarte
In reply to this post by John-375
I already did this before but it was not accepted.

For myself I did a modification on sqlite3 to allow the use of "AS" on
delete/update statements.

You can see the parser part here
https://github.com/mingodad/sqlite/blob/decimal64/src/parse.y .

Cheers !


On 06/07/17 05:16, John McMahon wrote:

> Hi
>
> Wondering if someone else can spot the syntax error in the following
> statement. "locns" is an attached database. There are four "AS" terms
> in the statement, they all alias tables.
>
> Ok, found it. It seems that an alias for an "UPDATE" table name is not
> permitted. Is there a particular reason for this?
> I would think it a convenience especially when using long table names
> and attached databases.
>
> John
>
> sqlite>     UPDATE locns.xxx_last_delivery AS tgt
>   ... >      SET
>   ... >         tgt.del_date =  (
>   ... >              SELECT src.last_del_d
>   ... >              FROM   main.updates AS src
>   ... >              WHERE  src.custnum = tgt.custnum),
>   ... >         tgt.del_qty = (
>   ... >              SELECT src.last_del_q
>   ... >              FROM   main.updates AS src
>   ... >              WHERE  src.custnum = tgt.custnum)
>   ... >      WHERE
>   ... >         tgt.custnum  = (
>   ... >              SELECT src.custnum
>   ... >              FROM   main.updates AS src
>   ... >              WHERE  src.last_del_d IS NOT NULL
>   ... >              AND    src.last_del_d > tgt.del_date)
>   ... >  ;
> Error: near "AS": syntax error
>

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

Re: syntax error near AS

John McMahon
In reply to this post by Keith Medcalf


On 06/07/2017 15:03, Keith Medcalf wrote:
>
> Do you know of any implementation of SQL that accepts an AS clause for the updated table?  I don't think any do.

No Keith, I don't. My only exposure to SQL is sqlite.

>
> Some versions have a FROM extension and you CAN specify an alias for the updated table in that clause, however, as far as I know the update table cannot be aliased and the "set <column> = ..." the <column> must always be a column in the updated table and while you may be allowed to "adorn" it in some implementations, any adornments are ignored (or trigger an error message if they are not the same as the updated table).
>

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203

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

Re: syntax error near AS

John McMahon
In reply to this post by Paul Sanderson


On 06/07/2017 16:04, Paul Sanderson wrote:
> The SQLite syntax diagrams are my first point of call when looking at an
> error in my code like this.
>
> https://sqlite.org/lang_update.html
>
> "AS" and an alias are clearly not part of the statement.

And that is how (with testing) I eventually worked out that I was on the
wrong track. Thank you, Paul.

>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 6 July 2017 at 06:03, Keith Medcalf <[hidden email]> wrote:
>
>>
>> Do you know of any implementation of SQL that accepts an AS clause for the
>> updated table?  I don't think any do.
>>
>> Some versions have a FROM extension and you CAN specify an alias for the
>> updated table in that clause, however, as far as I know the update table
>> cannot be aliased and the "set <column> = ..." the <column> must always be
>> a column in the updated table and while you may be allowed to "adorn" it in
>> some implementations, any adornments are ignored (or trigger an error
>> message if they are not the same as the updated table).
>>
>> --
>> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>>
>>> -----Original Message-----
>>> From: sqlite-users [mailto:[hidden email]]
>>> On Behalf Of John McMahon
>>> Sent: Wednesday, 5 July, 2017 21:17
>>> To: SQLite Users
>>> Subject: [sqlite] syntax error near AS
>>>
>>> Hi
>>>
>>> Wondering if someone else can spot the syntax error in the following
>>> statement. "locns" is an attached database. There are four "AS" terms in
>>> the statement, they all alias tables.
>>>
>>> Ok, found it. It seems that an alias for an "UPDATE" table name is not
>>> permitted. Is there a particular reason for this?
>>> I would think it a convenience especially when using long table names
>>> and attached databases.
>>>
>>> John
>>>
>>> sqlite>     UPDATE locns.xxx_last_delivery AS tgt
>>>     ... >      SET
>>>     ... >         tgt.del_date =  (
>>>     ... >              SELECT src.last_del_d
>>>     ... >              FROM   main.updates AS src
>>>     ... >              WHERE  src.custnum = tgt.custnum),
>>>     ... >         tgt.del_qty = (
>>>     ... >              SELECT src.last_del_q
>>>     ... >              FROM   main.updates AS src
>>>     ... >              WHERE  src.custnum = tgt.custnum)
>>>     ... >      WHERE
>>>     ... >         tgt.custnum  = (
>>>     ... >              SELECT src.custnum
>>>     ... >              FROM   main.updates AS src
>>>     ... >              WHERE  src.last_del_d IS NOT NULL
>>>     ... >              AND    src.last_del_d > tgt.del_date)
>>>     ... >  ;
>>> Error: near "AS": syntax error
>>>
>>> --
>>> Regards
>>>      John McMahon
>>>         [hidden email]
>>>
>>>
>>> _______________________________________________
>>> 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
>

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203

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

Re: syntax error near AS

John McMahon
In reply to this post by Clemens Ladisch


On 06/07/2017 16:33, Clemens Ladisch wrote:
> John McMahon wrote:
>> an alias for an "UPDATE" table name is not permitted. Is there a particular reason for this?
>
> The UPDATE statement affects a single table.  While an alias might be
> a convenience, it is not necessary (any naming conflicts in subqueries
> can be resolved by using an alias on the table(s) used there).

Thank you Clemens, I see now, the need for no ambiguities when updating.


>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203

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

Re: syntax error near AS

John McMahon
In reply to this post by Domingo Alvarez Duarte

On 06/07/2017 17:01, Domingo Alvarez Duarte wrote:
> I already did this before but it was not accepted.
>
> For myself I did a modification on sqlite3 to allow the use of "AS" on
> delete/update statements.
>
> You can see the parser part here
> https://github.com/mingodad/sqlite/blob/decimal64/src/parse.y .
>
> Cheers !

Thank you Domingo, but that option is beyond my programming competence.


>
>
> On 06/07/17 05:16, John McMahon wrote:
>> Hi
>>
>> Wondering if someone else can spot the syntax error in the following
>> statement. "locns" is an attached database. There are four "AS" terms
>> in the statement, they all alias tables.
>>
>> Ok, found it. It seems that an alias for an "UPDATE" table name is not
>> permitted. Is there a particular reason for this?
>> I would think it a convenience especially when using long table names
>> and attached databases.
>>
>> John
>>
>> sqlite>     UPDATE locns.xxx_last_delivery AS tgt
>>   ... >      SET
>>   ... >         tgt.del_date =  (
>>   ... >              SELECT src.last_del_d
>>   ... >              FROM   main.updates AS src
>>   ... >              WHERE  src.custnum = tgt.custnum),
>>   ... >         tgt.del_qty = (
>>   ... >              SELECT src.last_del_q
>>   ... >              FROM   main.updates AS src
>>   ... >              WHERE  src.custnum = tgt.custnum)
>>   ... >      WHERE
>>   ... >         tgt.custnum  = (
>>   ... >              SELECT src.custnum
>>   ... >              FROM   main.updates AS src
>>   ... >              WHERE  src.last_del_d IS NOT NULL
>>   ... >              AND    src.last_del_d > tgt.del_date)
>>   ... >  ;
>> Error: near "AS": syntax error
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203

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

Re: syntax error near AS

John-375
In reply to this post by Keith Medcalf


On 06/07/2017 15:03, Keith Medcalf wrote:
>
> Do you know of any implementation of SQL that accepts an AS clause for the updated table?  I don't think any do.

No Keith, I don't. My only exposure to SQL is sqlite.

>
> Some versions have a FROM extension and you CAN specify an alias for the updated table in that clause, however, as far as I know the update table cannot be aliased and the "set <column> = ..." the <column> must always be a column in the updated table and while you may be allowed to "adorn" it in some implementations, any adornments are ignored (or trigger an error message if they are not the same as the updated table).
>

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203


--
Regards
    John McMahon
       [hidden email]


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

Re: syntax error near AS

John-375
In reply to this post by Paul Sanderson


On 06/07/2017 16:04, Paul Sanderson wrote:
> The SQLite syntax diagrams are my first point of call when looking at an
> error in my code like this.
>
> https://sqlite.org/lang_update.html
>
> "AS" and an alias are clearly not part of the statement.

And that is how (with testing) I eventually worked out that I was on the
wrong track. Thank you, Paul.

>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 6 July 2017 at 06:03, Keith Medcalf <[hidden email]> wrote:
>
>>
>> Do you know of any implementation of SQL that accepts an AS clause for the
>> updated table?  I don't think any do.
>>
>> Some versions have a FROM extension and you CAN specify an alias for the
>> updated table in that clause, however, as far as I know the update table
>> cannot be aliased and the "set <column> = ..." the <column> must always be
>> a column in the updated table and while you may be allowed to "adorn" it in
>> some implementations, any adornments are ignored (or trigger an error
>> message if they are not the same as the updated table).
>>
>> --
>> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>>
>>> -----Original Message-----
>>> From: sqlite-users [mailto:[hidden email]]
>>> On Behalf Of John McMahon
>>> Sent: Wednesday, 5 July, 2017 21:17
>>> To: SQLite Users
>>> Subject: [sqlite] syntax error near AS
>>>
>>> Hi
>>>
>>> Wondering if someone else can spot the syntax error in the following
>>> statement. "locns" is an attached database. There are four "AS" terms in
>>> the statement, they all alias tables.
>>>
>>> Ok, found it. It seems that an alias for an "UPDATE" table name is not
>>> permitted. Is there a particular reason for this?
>>> I would think it a convenience especially when using long table names
>>> and attached databases.
>>>
>>> John
>>>
>>> sqlite>     UPDATE locns.xxx_last_delivery AS tgt
>>>     ... >      SET
>>>     ... >         tgt.del_date =  (
>>>     ... >              SELECT src.last_del_d
>>>     ... >              FROM   main.updates AS src
>>>     ... >              WHERE  src.custnum = tgt.custnum),
>>>     ... >         tgt.del_qty = (
>>>     ... >              SELECT src.last_del_q
>>>     ... >              FROM   main.updates AS src
>>>     ... >              WHERE  src.custnum = tgt.custnum)
>>>     ... >      WHERE
>>>     ... >         tgt.custnum  = (
>>>     ... >              SELECT src.custnum
>>>     ... >              FROM   main.updates AS src
>>>     ... >              WHERE  src.last_del_d IS NOT NULL
>>>     ... >              AND    src.last_del_d > tgt.del_date)
>>>     ... >  ;
>>> Error: near "AS": syntax error
>>>
>>> --
>>> Regards
>>>      John McMahon
>>>         [hidden email]
>>>
>>>
>>> _______________________________________________
>>> 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
>

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203


--
Regards
    John McMahon
       [hidden email]


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

Re: syntax error near AS

John-375
In reply to this post by Clemens Ladisch


On 06/07/2017 16:33, Clemens Ladisch wrote:
> John McMahon wrote:
>> an alias for an "UPDATE" table name is not permitted. Is there a particular reason for this?
>
> The UPDATE statement affects a single table.  While an alias might be
> a convenience, it is not necessary (any naming conflicts in subqueries
> can be resolved by using an alias on the table(s) used there).

Thank you Clemens, I see now, the need for no ambiguities when updating.


>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203


--
Regards
    John McMahon
       [hidden email]


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

Re: syntax error near AS

John-375
In reply to this post by Domingo Alvarez Duarte

On 06/07/2017 17:01, Domingo Alvarez Duarte wrote:
> I already did this before but it was not accepted.
>
> For myself I did a modification on sqlite3 to allow the use of "AS" on
> delete/update statements.
>
> You can see the parser part here
> https://github.com/mingodad/sqlite/blob/decimal64/src/parse.y .
>
> Cheers !

Thank you Domingo, but that option is beyond my programming competence.


>
>
> On 06/07/17 05:16, John McMahon wrote:
>> Hi
>>
>> Wondering if someone else can spot the syntax error in the following
>> statement. "locns" is an attached database. There are four "AS" terms
>> in the statement, they all alias tables.
>>
>> Ok, found it. It seems that an alias for an "UPDATE" table name is not
>> permitted. Is there a particular reason for this?
>> I would think it a convenience especially when using long table names
>> and attached databases.
>>
>> John
>>
>> sqlite>     UPDATE locns.xxx_last_delivery AS tgt
>>   ... >      SET
>>   ... >         tgt.del_date =  (
>>   ... >              SELECT src.last_del_d
>>   ... >              FROM   main.updates AS src
>>   ... >              WHERE  src.custnum = tgt.custnum),
>>   ... >         tgt.del_qty = (
>>   ... >              SELECT src.last_del_q
>>   ... >              FROM   main.updates AS src
>>   ... >              WHERE  src.custnum = tgt.custnum)
>>   ... >      WHERE
>>   ... >         tgt.custnum  = (
>>   ... >              SELECT src.custnum
>>   ... >              FROM   main.updates AS src
>>   ... >              WHERE  src.last_del_d IS NOT NULL
>>   ... >              AND    src.last_del_d > tgt.del_date)
>>   ... >  ;
>> Error: near "AS": syntax error
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203


--
Regards
    John McMahon
       [hidden email]


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...