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