One idea I came up with a while ago is to use a trigger on the "FROM"
table to cause updates to the main table:
CREATE TRIGGER UpdateTrigger AFTER UPDATE OF TriggerField ON Updates
BEGIN
UPDATE Master SET
Field1 = OLD.Field1,
Field2 = OLD.Field2,
...
WHERE Master.Key = OLD.Key
END;
and trigger the update with:
UPDATE Updates SET TriggerField = NULL ;
It seems to run (in my very simple test) at the same speed as REPLACE
INTO but has the advantages that (a) it doesn't replace rows (possibly
affecting rowids) and (b) not having to specify unchanged fields.
See http://stackoverflow.com/a/22481731/2096401 for more details.
Graham Holden
sql...@aldurslair.com
Monday, June 06, 2016, 9:11:14 AM, Hick Gunter <h...@scigames.at> wrote:
> 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
