This page is about the upsert statement in Sqlite
The Sqlite upsert syntax can be found at Upsert.
This page shows you some example
This example shows statement based on the category table d_category with the cat_id primary key column
An upsert with a select statement
insert into "d_category"
(
"cat_id",
"cat_desc",
"load_timestamp" )
select
"cat_id",
"cat_desc",
"load_timestamp"
from
"d_category"
order by
"cat_id"
on conflict ("cat_desc") do -- the following statement
update
set
"load_timestamp" = EXCLUDED."load_timestamp"
With literal values
insert
into
"d_category" ( "cat_id",
"cat_desc",
"load_timestamp" )
values ( 1, 'desc', '2020-11-17 13:20:38.005' )
on conflict ("cat_desc") do -- execute the following statement
update
set
"load_timestamp" = EXCLUDED."load_timestamp"
With bind variable
insert
into
"d_category" ( "cat_id",
"cat_desc",
"load_timestamp" )
values ( ?, ?, ? )
on conflict ("cat_desc") do -- execute the below statment
update
set
"load_timestamp" = EXCLUDED."load_timestamp"
When there is no where word, you need to insert where true in order to resolve the 2-1 parsing ambiguity
insert into "d_category" (
"cat_id",
"cat_desc",
"load_timestamp" )
select
"cat_id",
"cat_desc",
"load_timestamp"
from
"d_category"
where true -- mandatory
on conflict ("cat_desc") do -- which SQL to execute if the cat_id exists
update set "load_timestamp" = EXCLUDED."load_timestamp"