UPSERT fails for INSERT expression with SELECT

Question

I ran into a problem with the UPSERT statement in SQLite. I have a master and a slave database, the updates to the slave database should be merged into the master database. I would like to use the UPSERT clause. This works for inserting values() but not for inserting from a subquery.

The documentation (at least the diagram) shows that it should work: https://www.sqlite.org/lang_insert.html

But I'm getting an error. Am I missing something?

sqlite> create table orig (id int unique, a int);
sqlite> create table copy (id int unique, a int);
sqlite> insert into orig values (1,0);
sqlite> insert into copy values (1,0);
sqlite> insert into orig values (1,1) on conflict do update set a=1;
sqlite> select * from orig;
1|1
sqlite> insert into orig select * from copy on conflict do update set a=2;
Parse error: near "do": syntax error
  insert into orig select * from copy on conflict do update set a=2;
                                    error here ---^

I also have tried different expressions, but no avail:

sqlite> insert into orig select * from copy on conflict do nothing;
Parse error: near "do": syntax error
  insert into orig select * from copy on conflict do nothing;
                                    error here ---^
sqlite> insert into orig select * from copy on conflict (id) do update set a=2;
Parse error: near "do": syntax error
  ert into orig select * from copy on conflict (id) do update set a=2;
                                      error here ---^

sqlite3 --version 3.40.1 2022-12-28

Answer 1

This is explained in the Parsing Ambiguity section of the manual's entry on UPSERT:

2.2. Parsing Ambiguity

When the INSERT statement to which the UPSERT is attached takes its values from a SELECT statement, there is a potential parsing ambiguity. The parser might not be able to tell if the "ON" keyword is introducing the UPSERT or if it is the ON clause of a join. To work around this, the SELECT statement should always include a WHERE clause, even if that WHERE clause is just "WHERE true".

Ambiguous use of ON:

INSERT INTO t1 SELECT * FROM t2
ON CONFLICT(x) DO UPDATE SET y=excluded.y;

Ambiguity resolved using a WHERE clause:

INSERT INTO t1 SELECT * FROM t2 WHERE true
ON CONFLICT(x) DO UPDATE SET y=excluded.y;

Therefore, in order to resolve your issue, you need to add the redundant where true just before the upsert's on:

insert into orig
select * from copy
where true  -- <<< this guy here
on conflict do update set a=2;

Live demo