Thanks Dan I couldn't find a reference to this behaviour in the documentation. Do you think it would be possible to add a few words?
On Mon, Feb 13, 2017 at 7:49 PM Dan Kennedy <[email protected]> wrote: > On 02/13/2017 01:44 PM, Dudu Markovitz wrote: > > Good morning > > > > While answering question on stackoverflow > > <http://stackoverflow.com/a/42197036/6336479>I have noticed a bug > related > > to UPDATE using correlated sub-query. > > The demonstration code can be found in the attached file bug_report.sql > and > > the results of the demonstration with some comments added are in > > bug_reports.txt. > > > > I'm using SQLite 3.9.2 on windows 64 bit. > > It's surprising, but a consequence of the way SQLite has always worked. > Consider: > > CREATE TABLE t1(a INTEGER PRIMARY KEY, b); > INSERT INTO t1 VALUES(1, 1); > INSERT INTO t1 VALUES(2, 0); > > Then: > > UPDATE t1 SET b=(SELECT b+1 FROM t1 WHERE a=1); > > In this case, the sub-query is uncorrelated and "b" is set to 2 in both > rows. > > But say you added some other term to the sub-query so that it was > correlated. It is then executed separately for each row. So SQLite > updates the first row (with a=1) and sets column "b" to 2. But then, > when it goes to update the next row, it runs the correlated query a > second time. And this time it returns 3. So you end up setting "b" in > the second row to 3 instead of 2. > > Something pretty similar is occurring in the example you posted. > > Dan. > > _______________________________________________ > 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
