UPSERT for “INSERT INTO tab SELECT * FROM another_tab”

How would I do “UPSERT” (INSERT OR UPDATE) into SQLite table when inserting multiple rows from another table.

I’ve tried:

INSERT INTO tab_name
SELECT * FROM tmp
ON CONFLICT(id)
    DO UPDATE SET
      val = excluded.val;

But it gives me:

syntax error near “DO”

What would be the correct and the most efficient way to achieve that?

Answer

You might have hit a documented trap called 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".)

So, does this work better?

INSERT INTO tab_name
SELECT * FROM tmp WHERE true
ON CONFLICT(id) DO UPDATE SET val = excluded.val;