Hi RBS, Re-introducing mytable in the sub-select is a workaround (and to get the same semantics in the general case I have to use the same row from the inner mytable and outer mytable). As indicated in my original message I already have a workaround for the issue.
The intention of my post was to report that sqlite incorrectly reports that mytable does not exists. Table mytable exists --- it is the target table of the UPDATE statement and still in scope. I see no reason why it is not allowed to use it in that EXISTS expression. So my question: is there a valid reason why sqlite does not know mytable.myfield2 in my simplified query? Or is this a bug? Regards, Rob Golsteijn UPDATE mytable SET myfield1 = (SELECT 1 from mytable ORDER BY EXISTS (SELECT 1 WHERE mytable.myfield2 = 1 ) ) RBS On Tue, Oct 25, 2016 at 4:40 PM, Rob Golsteijn <rob.golste...@mapscape.eu> wrote: > Hi List, > > I encountered a situation in which sqlite does not understand to which > field I try to refer. I simplified the original query to show the problem. > The simplified query itself is now completely meaningless (and for my > specific situation I could rewrite the query to work around the problem). > > In the example below I expected that mytable.myfield2 in the EXISTS > expression would refer to myfield2 of table mytable from the top level > UPDATE statement. It looks like the combination of an ORDER BY and an > EXISTS that refers to the table of an UPDATE statement causes sqlite to > report that myfield2 is unknown. Sqlite does not complain when I use > mytable.myfield2 in other places in the query. > > CREATE TABLE mytable > ( > myfield1 INTEGER, > myfield2 INTEGER > ); > > UPDATE mytable > SET myfield1 = (SELECT 1 > ORDER BY EXISTS (SELECT 1 > WHERE mytable.myfield2 =1 > ) > ); > > Error: no such column: mytable.myfield2 > > Tested with sqlite versions 3.8.4.3, 3.8.8.2, 3.11.1 and 3.15.0 > (latest). > > Met Vriendelijke Groet, Kind Regards, > > Rob Golsteijn _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users