On 2016/10/25 5:44 PM, Bart Smissaert wrote:
Try this:
UPDATE mytable
SET myfield1 = (SELECT 1 from mytable
ORDER BY EXISTS (SELECT 1
WHERE mytable.myfield2 = 1
)
)
RBS
Thing is, what you propose specifically negates the point the OP is
trying to make, in that a sub-query of a sub-query can't see the main
query definitions when specified in an ORDER BY clause.
Your fix puts the definitions within reach, which simply hides the problem.
Myself, I do not actually know whether definitions in a SELECT /can/ in
fact be seen n-levels of sub-query deep... It doesn't seem to be
documented in SQLite and I can't find an SQL-Standard reference now that
specifically demands it, but it seems possible (in SQLite at least) when
the references happen in the SELECT or WHERE clauses of the
sub-sub-sub-query. It however doesn't work when referred-to in the ORDER
BY clause of the same - which is the OP's original point I think.
So to be clear, I do not know which way this should fly, but it does
seem inconsistent in the current implementation. (Perhaps inconsistent
is not the right word, it doesn't change from edition to edition, it
just seem to not act the same way in all clauses, although consistently
so through the latest editions.)
Cheers,
Ryan
PS to the OP: Kudos for not labeling it a "bug", Rob.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
