On Tue, Feb 24, 2009 at 6:12 PM, John Machin <sjmac...@lexicon.net> wrote: > On 25/02/2009 10:30 AM, P Kishor wrote: >> >> On Tue, Feb 24, 2009 at 5:19 PM, Leo Freitag <leofrei...@netcologne.de> >> wrote: >>> >>> Hallo, >>> >>> I'm trying to insert the highest value of tblName into tblZO. >>> >>> There fore I followed the hints in this article ... >>> >>> http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--td19085514.html#a19085514 >>> >>> ... but I got a error (see below) >>> >>> SQLite version 3.3.13 >>> Enter ".help" for instructions >>> sqlite> create 'tblName' ('id' integer primary key, 'text' text); >>> SQL error: near "'tblName'": syntax error >>> sqlite> create table 'tblName' ('id' integer primary key, 'text' text); >>> sqlite> insert into 'tblName' Values (NULL, 'one'); >>> sqlite> insert into 'tblName' Values (NULL, 'two'); >>> sqlite> insert into 'tblName' Values (NULL, 'three'); >>> sqlite> insert into 'tblName' Values (NULL, 'four'); >>> sqlite> insert into 'tblName' Values (NULL, 'five'); >>> sqlite> create table 'tblRefMaxName' ('ref' integer, 'nn' text); >>> sqlite> insert into 'tblRefMaxName' Values (select max(id) from tblName, >>> 'eins') >> >> select max(id) from tblName, 'eins' is not a valid select statement. > > That is not the problem. Consider this:
actually it is the problem. I wasn't clear in my explanation so that is my fault. the following is a valid statement: SELECT Max(id) FROM tbl but the following is problematic: SELECT Max(id) FROM tbl, 'eins' because the SQL parser is looking for a table called 'eins' Wrapping the first part in parens removes that ambiguity. > > sqlite> insert into 'tblRefMaxName' Values (NULL, select max(text) from > tblName); > SQL error: near "select": syntax error > > select max(text) from tblName *IS* a valid select statement. Having a valid > select statement is of course necessary but it is not sufficient. At this > stage an expr is expected, and the only way the syntax railroad diagram > allows you to get a select statement in there is: > --- ( select-stmt ) --- > > i.e. like this: > > sqlite> insert into 'tblRefMaxName' Values (NULL, (select max(text) from > tblName)); > sqlite> select * from tblRefMaxName; > 5|eins > |two > sqlite> > > Note that if the table had only one column you would still need the > parentheses: > > insert into 'tblRefMaxName' Values ((select max(text) from tblName)); > > GENERAL RULE: always wrap an inner select in parentheses, whether it's being > used as an expression or as a join-source. > > HTH, > John > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ Sent from: Madison WI United States. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
