>
>
>
> A SELECT in an UPSERT should always contain a WHERE clause. Like this:
>
> INSERT INTO t1(a) SELECT a FROM t2 WHERE true ON CONFLICT(A) DO NOTHING;
>
> Without the WHERE clause, the ON keyword tricks the parser into
> thinking it is processing an "ON" join constraint on the FROM clause.
> The WHERE clause resolves the ambiguity. The extra no-op WHERE clause
> is omitted from the prepared statement by the query optimizer and
> hence does not slow down the execution of the statement.
>
> This is a known limitation of the UPSERT syntax. I had intended to
> document it, but I apparently forgot to do so, or at least I cannot
> find where I documented it right this second. It is a messy situation
> that comes about due to our use of an LALR(1) parser (parsers with
> more lookahead also run slower) and by the need to provide full
> backwards compatibility with older versions of SQLite.
>
>
Thanks. Easy to live with! The first time I used UPSERT with a SELECT it
was a JOIN ending with USING(). Worked like a charm!
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
