Sqlite - Upsert

About

This page is about the upsert statement in Sqlite

The Sqlite upsert syntax can be found at Upsert.

This page shows you some example

Articles Related

Example

This example shows statement based on the category table d_category with the cat_id primary key column

With a select statement

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

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"

Bind variable

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"

Parsing Ambiguity

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"