[SOLVED] On Conflict with SELECT from a table results in Parse error near line 11: near "DO"

On Conflict with SELECT from a table results in Parse error near line 11: near "DO"

I have the following SQL statement:

INSERT INTO names (first, last) 
    SELECT first, last 
    FROM tmp  
         ON CONFLICT(first) DO UPDATE SET seen = seen + 1;

I get an error:

Parse error near line 11: near "DO": syntax error

However, if I directly insert the values manually, it works:

INSERT INTO names (first, last) 
VALUES ('Bob', 'Smith')  
    ON CONFLICT(first) DO UPDATE SET seen = seen + 1;

Why can't I upsert the records from a SELECT statement?

Solution

The documentation talks about this here:

https://www.sqlite.org/lang_upsert.html

When you use INSERT with SELECT, there is a potential parsing ambiguity. The parser might not be able to tell if the "ON" keyword is introducing the conflict clause 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".