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