Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

2017-02-16 Thread James K. Lowden
On Wed, 15 Feb 2017 12:06:58 +0100 Marek Wieckowski wrote: > 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

Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

2017-02-15 Thread Marek Wieckowski
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

Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

2017-02-14 Thread Simon Slavin
On 14 Feb 2017, at 4:42pm, David Raymond wrote: > Correct. Thanks, David. I'd completely missed what this syntax allowed you to do. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

2017-02-14 Thread David Raymond
Correct. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, February 14, 2017 11:08 AM To: SQLite mailing list Subject: Re: [sqlite] bug: fields from external (being updated) table cannot be used in

Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

2017-02-14 Thread Simon Slavin
On 14 Feb 2017, at 3:55pm, David Raymond wrote: > It's not. It sets up an alias to a new view of test. In the where clause the > i.whereField is referring to that new view of the whole table, whereas the > test.whereField is referring to the field in the current

Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

2017-02-14 Thread David Raymond
It's not. It sets up an alias to a new view of test. In the where clause the i.whereField is referring to that new view of the whole table, whereas the test.whereField is referring to the field in the current record of test that's being updated. So if the table is things, and whereField is the

Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

2017-02-14 Thread Simon Slavin
On 14 Feb 2017, at 3:36pm, James K. Lowden wrote: > UPDATE test > SET value = value + 100 * ( > SELECT min(i.value) -- or max, or something > FROM test i > WHEREi.whereField =

Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

2017-02-14 Thread James K. Lowden
On Tue, 14 Feb 2017 15:06:16 +0100 Marek Wieckowski wrote: > UPDATE test > SET value = value + 100 *( > SELECT i.value > FROM test i > WHEREi.whereField = test.whereField > ORDER BY

[sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

2017-02-14 Thread Marek Wieckowski
Hi, Basically: in a subselect in an update statement all fields from the row which is being updated CAN be used in SELECT and WHERE clauses (see the first update below using test.whereField in the WHERE clause), but using them in ORDER BY leads to an error. Example: create table test (