What is the recommended way to update multiple columns from a single select?

(1) By PazO (JohnSmith) on 2021-05-13 09:27:16 [link] [source]

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...

My question:
Is there a better way to do this update?

Thank you for any advice

(2) By Rado (antlor) on 2021-05-13 10:15:50 in reply to 1 [link] [source]

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

(3) By Richard Hipp (drh) on 2021-05-13 11:59:02 in reply to 1 [link] [source]

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

(4) By PazO (JohnSmith) on 2021-05-13 12:29:50 in reply to 2 [source]

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] [1] [SQLITE_ERROR] SQL error or missing database (no such column: src.val_a)

(5) By PazO (JohnSmith) on 2021-05-13 12:30:22 in reply to 3 [link] [source]

Thanks you very much

This is working

(6) By Rado (antlor) on 2021-05-13 12:46:46 in reply to 4 [link] [source]

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.

(7) By Keith Medcalf (kmedcalf) on 2021-05-13 13:17:56 in reply to 2 [link] [source]

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.

(8) By PazO (JohnSmith) on 2021-05-13 14:57:30 in reply to 7 [link] [source]

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.