Hi James, Thanks for your answer.
Yes, there are lots of different queries that you might want to write _instead_ - in your example you have changed the logic of the update... But you could also rewrite the original query to keep the original logic in such a way that it would work in sqlite (see e.g. the stackoverflow page http://stackoverflow.com/questions/39350537/sqlite-update-select-query-referencing-table-being-updated-in-order-by-clause/ - the simplest is to ... add an extra level of sub-select, select the value you want to order by (so you use an "external" field in SELECT) and then order by the value from sub-subselect... Super ugly and harder to understand.). The thing is that in principle there is nothing wrong with using test.xxx fields in the subselect: there really should be no difference whether you use them in "where" or "order by"... The fact that sqlite does not allow them to be used in ORDER BY (while allowing in SELECT and WHERE) imho is simply a bug. You don't want to force users to write ugly workarounds. Oh, and btw: the same syntax (with using an external field in ORDER BY of a subselect) simply works e.g. in update statements postgres. Best, Marek On Tue, Feb 14, 2017 at 4:36 PM, James K. Lowden <jklow...@schemamania.org> wrote: > On Tue, 14 Feb 2017 15:06:16 +0100 > Marek Wieckowski <wiec...@gmail.com> wrote: > > > UPDATE test > > SET value = value + 100 *( > > SELECT i.value > > FROM test i > > WHERE i.whereField = test.whereField > > ORDER BY i.orderField = test.orderField > > LIMIT 1 > > ); > > > > Error: no such column: test.orderField > > There are other errors, too. The update is nondeterministic and > nonstandard. Does this not serve the purpose better? > > UPDATE test > SET value = value + 100 * ( > SELECT min(i.value) -- or max, or something > FROM test i > WHERE i.whereField = test.whereField > ); > > --jkl > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
