On Mon, 19 May 2014 09:55:25 +0300 Paul <de...@ukr.net> wrote: > > UPDATE adla1 > > SET pflopf = ( > > SELECT pflopf > > FROM adl > > WHERE adl.ref = adla1.ref) > > WHERE ( > > SELECT COUNT(*) > > FROM ( > > SELECT 1 FROM adl > > WHERE adl.ref = adla1.ref > > LIMIT 2 > > ) > > ) = 1; > > > > Not all sure what LIMIT 2 does there. I think a SQL-92 version > > would be > > > > Limit, limits number of rows selected by ref. If we have found 2, no > need to lookup further. That's why it was said to be a slightly > optimized version. Generally this saves some disk reads.
I'll take your word for it, because it's redundant. Although the WHERE clause is rather awkwardly expressed, it does contain sufficient information that the query planner could skip any "adl" as soon as it sees that COUNT(*) will exceed 1. My rewrite used HAVING. I would hope that SQLite wouldn't insist on computing all aggregations for a WHERE EXISTS ... HAVING correlated subquery. That would be, er, suboptimal. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
