SQLite does not have a merge keyword, instead, it comes with an upsert clause (but not an upsert keyword) which is an addition to the insert statement.
The following example tries to insert the same primary key twice. Because of the on conflict do nothing clause, the second insertion of id=3 causes no error message and does not modify the record whose id is 3.
create table T (
id integer primary key,
val text
);
insert into T values (1, 'one' ) on conflict do nothing;
insert into T values (2, 'two' ) on conflict do nothing;
insert into T values (3, 'trhee') on conflict do nothing; -- Note the typo!
insert into T values (3, 'three') on conflict do nothing;
select * from T;
--
-- 1|one
-- 2|two
-- 3|trhee
With on conflict(pk_name) do update, it's possible to override some values in the destination table if a primary key already exists:
create table T (
id integer primary key,
val text
);
insert into T values (1, 'one' ) on conflict(id) do update set val=excluded.val;
insert into T values (2, 'two' ) on conflict(id) do update set val=excluded.val;
insert into T values (3, 'trhee') on conflict(id) do update set val=excluded.val; -- Note the typo
insert into T values (3, 'three') on conflict(id) do update set val=excluded.val;
select * from T;
--
-- 1|one
-- 2|two
-- 3|three
The rowid of a table that stores unique values can be selected for new and existing values like so:
drop table if exists tq84_t;
create table tq84_t(id integer primary key, val text not null unique);
insert into tq84_t(val) values ('one' ) on conflict do update set val = val returning rowid;
insert into tq84_t(val) values ('two' ) on conflict do update set val = val returning rowid;
insert into tq84_t(val) values ('three') on conflict do update set val = val returning rowid;
insert into tq84_t(val) values ('two' ) on conflict do update set val = val returning rowid;
insert into tq84_t(val) values ('four' ) on conflict do update set val = val returning rowid;
See also
The on conflict clause (which applies to unique, not null, check and primary key, but not foreign key, constraints).
The replace statement (which is an alias for insert or replace).