INSERT INTO with subquery and ON CONFLICT-CodePudding

I want to insert all elements from a JSON array into a table:

INSERT INTO local_config(parameter, value)
SELECT json_extract(j.value, '$.parameter'), json_extract(j.value, '$.value')
FROM json_each(json('[{"parameter": 1, "value": "value1"}, {"parameter": 2, "value": "value2"}]')) AS j
WHERE value LIKE '%'
ON CONFLICT (parameter) DO UPDATE SET value = excluded.value;

This works so far, but do I really need the WHERE value LIKE '%' clause?

When I remove it:

INSERT INTO local_config(parameter, value)
SELECT json_extract(j.value, '$.parameter'), json_extract(j.value, '$.value')
FROM json_each(json('[{"parameter": 1, "value": "value1"}, {"parameter": 2, "value": "value2"}]')) AS j
ON CONFLICT (parameter) DO UPDATE SET value = excluded.value;

I get this error:

[SQLITE_ERROR] SQL error or missing database (near "DO": syntax error)

CodePudding user response：

From SQL As Understood By SQLite/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 you need the WHERE clause, but it can be a simple WHERE true or just WHERE 1.