SQLite Forum

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 ) 
	);


<u>What I want to do:</u><br>
	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...

<u>My question:</u><br>
<b>Is there a better way to do this update?</b>

Thank you for any advice