Would not Replace into A (<fieldlist>) select <key_fields>,<unchanged_fields>, <changed_fields> from A [cross] join B on( <key_fields>);
do the trick? If a.rowid has an alias (i.e. integer primary key) then the modified rows would be deleted, but reinserted with their respective previous rowids. This may required switching foreign keys off fort he duration of the update. -----Ursprüngliche Nachricht----- Von: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von skywind mailing lists Gesendet: Samstag, 04. Juni 2016 18:19 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] UPDATE statement without FROM clause Hi, why? At the moment I have to run something like: UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID); Using a FROM clause I just need one scan through B (at least in principle). Now, I need N+1 scans. Regards, Hartwig > Am 2016-06-04 um 15:33 schrieb Gerry Snyder <mesmerizer...@gmail.com>: > > If SQLite implemented the FROM it would just be a translation into the > complex and slow statements you want to avoid. > > Gerry Snyder > On Jun 4, 2016 9:19 AM, "skywind mailing lists" > <mailingli...@skywind.eu> > wrote: > >> Hi, >> >> I am using quite often SQL statements that update the data of one >> table with data from another table. This leads to some quite complex >> (and slow) statements because SQLite3 is not supporting a FROM clause >> in update statements. I am just wondering why the FROM clause is not >> supported by SQLite3?! Is this too complex to implement or is there >> simply no demand for these type of statements? >> >> Regards, >> Hartwig >> >> >> >> _______________________________________________ >> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
