What is the recommended way to update multiple columns from a single select?
Assuming I have the following table:
CREATE TABLE MyTable ( id integer NOT NULL PRIMARY KEY , object_id integer NOT NULL , snapshot_id integer NOT NULL , val_a integer , val_b integer , val_c integer CONSTRAINT Idx_MyTable_ObjectInSnapshot UNIQUE ( object_id, snapshot_id ) );
What I want to do:
For each objec I want to copy values (a,b,c) from snapshot 1 to snapshot 2.
My naive thinking was something like:
UPDATE MyTable SET val_a=src.val_a, val_b=src.val_b, val_c=src.val_c INNERT JOIN MyTable src ON object_id=src.object_id WHERE snapshot_id=2 AND src.snapshot_id=1
But this is not supported by sqlite.
I read that I can use UPSERT:
INSERT INTO .. ON CONFLICT(...) DO UPDATE SET...
The only thing is that I know in advanced that the INSERT will fail and it seems not efficient performance-wise to go through the:
- Lock for insert,
- Fail on insert,
- Do the update...
Is there a better way to do this update?
Thank you for any advice
You have two update options: 1. update update MyTable set val_a = src.val_a, val_b = src.val_b, val_c = src.val_c from (select * from MyTable where snapshot_id = 1) as src where MyTable.object_id = src.object_id and MyTable.snapshot_id = 2 2. update update MyTable set val_a = (select x.val_a from MyTable x where x.snapshot_id = 1 and x.object_id = MyTable.object_id), val_b = (select x.val_b from MyTable x where x.snapshot_id = 1 and x.object_id = MyTable.object_id), val_c = (select x.val_c from MyTable x where x.snapshot_id = 1 and x.object_id = MyTable.object_id) where snapshot_id = 2
Thank you for the reply
Are we sure option 1 is working?
It gives me the following errors:
[2021-05-13 15:27:44] Unsafe query: 'Update' statement without 'where' updates all table rows at once
[2021-05-13 15:28:08]  [SQLITE_ERROR] SQL error or missing database (no such column: src.val_a)
Take a look into this: https://www.sqlite.org/lang_update.html section 2.2. UPDATE FROM I have tested (on last version) before I send it but you must have at least SQLite version 3.33.0 (2020-08-14). If you got error then you have older version.
Why all the complication in the first update.
update MyTable as dst set val_a = src.val_a, val_b = src.val_b, val_c = src.val_c from MyTable as src where src.object_id == dst.object_id and dst.snapshot_id == 2 and src.snapshot_id == 1 ;
It is pretty much a direct translation from English into SQL.
For number 2 a better method (if you are using an old version of SQLite3 that does not have UPDATE FROM) would be:
update MyTable as dst set (val_a, val_b, val_c) = (select val_a, val_b, val_c from MyTable where object_id == dst.object_id and snapshot_id == 1) where snapshot_id == 2 ;
You would only need to use multiple correlated subqueries for really ancient versions of SQLite3 that does not have row-tuples.
Thank you for this reply
My sqlite version is 3.34.0.
According to JetBrains DataGrip (the application I am using to test my queries) this version is the latest-stable for sqlite.
The UPDATE FROM example you gave above is working very good for me, thank you!
For some reason the UPDATE FROM example with the (SELECT...) gives me the error.
UPDATE MyTable SET (val_a,val_b,val_c) = (SELECT x.val_a, x.val_b, x.val_c FROM MyTable AS x WHERE x.snapshot_id=1 AND x.object_id=MyTable.object_id) WHERE snapshot_id=2