Error while doing UPSERT in Sqlite 3.34 - Error: near "DO": syntax error [duplicate]

Question

I am trying to load a daily CSV load file into a main table, so that all new records are inserted and all existing records are updated. I am using UPSERT feature of Sqlite for this purpose.

Here is my shell script.

LOAD_FILE="/tmp/main.csv"
LOAD_TABLE="tbl_tmp_main"
MAIN_TABLE="tbl_main"
COLUMNS="t_id, t_col1, t_col2"


CREATE_TABLE_QUERY="DROP TABLE IF EXISTS $LOAD_TABLE; CREATE TABLE $LOAD_TABLE AS SELECT $COLUMNS FROM $MAIN_TABLE WHERE false"
LOAD_TABLE_QUERY=".separator ','\n.import '$LOAD_FILE' $LOAD_TABLE"
UPSERT_TABLE_QUERY="
    INSERT INTO $MAIN_TABLE($COLUMNS) SELECT $COLUMNS FROM $LOAD_TABLE
    ON CONFLICT(t_id) DO UPDATE
    SET
        t_col1 = excluded.t_col1,
        t_col2 = excluded.t_col2
    ;
"

echo ""
echo "$CREATE_TABLE_QUERY" | sqlite3 mydatabase.sqlite3            # <-- This works
echo "[INFO] Temporary Table Created."

echo "$LOAD_TABLE_QUERY" | sqlite3 mydatabase.sqlite3              # <-- This works
echo "[INFO] Data Loaded into Temporary Table."

echo "$UPSERT_TABLE_QUERY" | sqlite3 mydatabase.sqlite3            # <-- This errors out
echo "[INFO] Records Inserted/Updated."
echo ""

Here is the error.

Error: near line 2: near "DO": syntax error

I tried to check the documentation, but I am not sure what is wrong in the query above. Can somebody point out why it isnt working.

Here is my sqlite version

-> sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ebd1f

Answer 1

The error is due to Parsing Ambiguity as mentioned in the documentation. You need to add WHERE true as part of the SELECT clause and that should fix it.

Here is your updated UPSERT query.

INSERT INTO $MAIN_TABLE($COLUMNS) SELECT $COLUMNS FROM $LOAD_TABLE  WHERE true
--                                                                  ^^^^^^^^^^
ON CONFLICT(t_id) DO UPDATE
SET
    t_col1 = excluded.t_col1,
    t_col2 = excluded.t_col2
;

Here is the section in Docs.

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".

Ambiguous use of ON:

INSERT INTO t1 SELECT * FROM t2
ON CONFLICT(x) DO UPDATE SET y=excluded.y;

Ambiguity resolved using a WHERE clause:

INSERT INTO t1 SELECT * FROM t2 WHERE true
ON CONFLICT(x) DO UPDATE SET y=excluded.y;