That is not a problem -- and I only fixed the where clause, not the set clause.
The data will be "hot" on the second access, so the overhead of the additional access is negligible since CPU usage is negligible (even if it goes through all the motions of cold access) compared to I/O usage -- given a large enough page cache in RAM (so no I/O is required) to hold the pages involved in the tree traversals, of course. It is also possible to construct a view which may be used with an instead-of trigger to achieve indirectly the exact behaviour implemented by executing an update query of the form: UPDATE a SET x=b.b FROM a, b WHERE a.a = b.a AND ... You simply need to create a view which outputs the rowid's needing updating, and the values that should be updated. Then the instead of update trigger on the view merely applies the updates to the underlying real table. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Igor Tandetnik >Sent: Monday, 8 December, 2014 18:32 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] How do I update multiple rows in a single sql >statement > >On 12/8/2014 8:20 PM, Keith Medcalf wrote: >> >> update temp_table >> set id=(select id from some_table where c=42), >> operation='UPDATE' >> where exists (select 1 >> from some_table s >> where s.a=temp_table.a and s.b=temp_table.b and s.c=42); >> >> is the proper way of phrasing of a correlated subquery ... > >Now the problem is that (select id from some_table where c=42) takes an >id from some row of some_table - not necessarily the row with matching a >and b. > >Without some form of UPDATE...FROM (supported by some SQL engines, but >not SQLite), I can't think of a way to avoid repeating the whole >three-conjuncts condition twice - once in SET id=, and again in WHERE. >-- >Igor Tandetnik > >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
