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:

1. Lock for insert,
2. Fail on insert,
3. Do the update...

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

Thank you for any advice

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

Thanks you very much

This is working