Re: [sqlite] syntax error near AS
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 ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
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 <kmedc...@dessus.com> 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 = ..." the 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:sqlite-users-boun...@mailinglists.sqlite.org] 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 ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error -- Regards John McMahon li...@jspect.fastmail.fm ___ 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 -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
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 = ..." the 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 j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
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 ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
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 <kmedc...@dessus.com> 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 = ..." the 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:sqlite-users-boun...@mailinglists.sqlite.org] 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 ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error -- Regards John McMahon li...@jspect.fastmail.fm ___ 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 -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
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 = ..." the 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 j...@jspect.fastmail.com.au 04 2933 4203 -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
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 ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
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 <kmedc...@dessus.com> 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 = ..." the 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:sqlite-users-boun...@mailinglists.sqlite.org] > > 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 > >... > ANDsrc.last_del_d > tgt.del_date) > >... > ; > > Error: near "AS": syntax error > > > > -- > > Regards > > John McMahon > >li...@jspect.fastmail.fm > > > > > > ___ > > 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] syntax error near AS
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 = ..." the 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:sqlite-users-boun...@mailinglists.sqlite.org] > 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 >... > ANDsrc.last_del_d > tgt.del_date) >... > ; > Error: near "AS": syntax error > > -- > Regards > John McMahon >li...@jspect.fastmail.fm > > > ___ > 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] 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 ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax error near "NOT"
What version are you using? The "IF NOT EXISTS" clause only exists in SQLite
3.3.0 and later.
- Pam
On 4/5/06, Lucky Luke <[EMAIL PROTECTED]> wrote:
>
> Ok, a VERY VERY strange error, which I can't solve.
>
> I use the SQLite.NET wrapper from www.phpguru.org to use it with C#.
>
> I have the following query:
>
> sql = "CREATE TABLE IF NOT EXISTS bot_users (" +
> "username VARCHAR(100)," +
> "password VARCHAR(100) " +
> ")";
>
> But when I try to execute this I get the following error:
>
> Sqlite.NET.SQLiteEception: near "NOT": syntax error
>
> There isn't something wrong in my query I thought..
> Greetings,
>
> LuckyLuke
>
>
[sqlite] Syntax error near "NOT"
Ok, a VERY VERY strange error, which I can't solve.
I use the SQLite.NET wrapper from www.phpguru.org to use it with C#.
I have the following query:
sql = "CREATE TABLE IF NOT EXISTS bot_users (" +
"username VARCHAR(100)," +
"password VARCHAR(100) " +
")";
But when I try to execute this I get the following error:
Sqlite.NET.SQLiteEception: near "NOT": syntax error
There isn't something wrong in my query I thought..
Greetings,
LuckyLuke
