24 Dec 2015

Many database engines implement on-conflict clause mechanism, with various syntaxes and algorithms.

In SQLite, such clause also exists. In particular, the INSERT/UPDATE OR IGNORE and INSERT/UPDATE OR REPLACE can serve as syntatic sugar for things like ‘insert without duplicates’ and ‘update when duplicated’. For this syntax to work, the corresponding constraints must be defined in the respected table schemas. Here is an example from my own code:

-- create table 'conversions'
create table if not exists conversions (
    id        integer primary key,
    input     integer,
    bundle    text,
    subscript text,
    procs     text,
    script    text,
    foreign key(input) references inputs(id),
    constraint uinput unique(input)

-- insert but avoid duplicates
insert or ignore into conversions (input, bundle, subscript, procs, script)
select i.id, t.bundle, t.subscript, t.procs, t.script
  from t_conversions t
  join inputs        i
    on t.inputloc = i.location;