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 type of thing, what's happening here is for each thing to increment its value by 100 times the least valuable thing of the same type. So what's being commented is that the "least valuable thing" is potentially changing after every updated row, rather than being a constant of "the least valuable thing as it stood at the start of the update" -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, February 14, 2017 10:41 AM To: SQLite mailing list Subject: Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect On 14 Feb 2017, at 3:36pm, James K. Lowden <jklow...@schemamania.org> wrote: > UPDATE test > SET value = value + 100 * ( > SELECT min(i.value) -- or max, or something > FROM test i > WHERE i.whereField = test.whereField > ); Someone please explain something to me ? One of my assumptions is wrong. The construction "FROM test i" is a short form of "FROM test AS i". This sets up an alias to the table "test" so you can call it "i" if you want to. Later on in that command I see "WHERE i.whereField = test.whereField". Under the circumstances is that not the same as "WHERE test.whereField = test.whereField" ? Simon. _______________________________________________ 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
