I have a problem with changing a 'INSERT OR REPLACE' query to a native upsert query.

(1) By daffie on 2021-02-07 11:35:45 [link] [source]

I have the following table: CREATE TABLE "test_people" ( "name" TEXT, "age" INTEGER, "job" TEXT, PRIMARY KEY("job") )

The following query works: INSERT OR REPLACE INTO "test_people" ("job", "age", "name") select json_extract(value, "$.job") as "job", json_extract(value, "$.age") as "age", json_extract(value, "$.name") as "name" from json_each('[{"job":"Presenter","age":31,"name":"Tiffany"},{"job":"Speaker","age":32,"name":"Meredith"}]')

It fails when I change it to: INSERT INTO "test_people" ("job", "age", "name") select json_extract(value, "$.job") as "job", json_extract(value, "$.age") as "age", json_extract(value, "$.name") as "name" from json_each('[{"job":"Presenter","age":31,"name":"Tiffany"},{"job":"Speaker","age":32,"name":"Meredith"}]') ON CONFLICT ("job") DO UPDATE SET "age" = EXCLUDED."age", "name" = EXCLUDED."name"

The changes are: the removal of the part 'OR REPLACE' and the adding of: 'ON CONFLICT ("job") DO UPDATE SET "age" = EXCLUDED."age", "name" = EXCLUDED."name"'

Is there something I do wrong or is it something that cannot work or is it a bug/feature request?

Any help is very much appreciated.

(2) By daffie on 2021-02-07 12:16:43 in reply to 1 [source]

Found the answer myself. You need to add 'WHERE TRUE' to the query.

INSERT INTO "test_people" ("job", "age", "name") select json_extract(value, "$.job") as "job", json_extract(value, "$.age") as "age", json_extract(value, "$.name") as "name" from json_each('[{"job":"Presenter","age":31,"name":"Tiffany"},{"job":"Speaker","age":32,"name":"Meredith"}]') WHERE TRUE ON CONFLICT ("job") DO UPDATE SET "age" = EXCLUDED."age", "name" = EXCLUDED."name"

For more info see the section 'Parsing Ambiguity' on the page: https://www.sqlite.org/lang_UPSERT.html