SQLite Forum

sql error:INSERT INTO tbl_des SELECT v1,v2.. FROM tbl_src on conflict(vehicle_id,begin_time) do nothing
Login

sql error:INSERT INTO tbl_des SELECT v1,v2.. FROM tbl_src on conflict(vehicle_id,begin_time) do nothing

(1) By eternal (yh2216) on 2021-04-15 07:49:25 [link]

sql:
INSERT INTO pathway SELECT pathold._rowid_,
                           pathold._shape,
                           pathold.vehicle_id,
                           pathold.begin_time,
                           pathold.end_time,
                           pathold.area,
                           pathold.rect,
                           pathold.block_id,
                           pathold.layer_index
                      FROM pathold on conflict(vehicle_id,begin_time) DO NOTHING;

And when exec the sql,the sqliteStudio tell me " Error while executing SQL query on database 'stratify_data': near "do": syntax error";
other info:
sqliteStudio version:3.2.1;
sqlite3 version:3.35.4(I have changed the sqlite3.dll in sqliteStudio myself).

tbl pathway's DDL:

CREATE TABLE IF NOT EXISTS pathway (
    id       INTEGER PRIMARY KEY UNIQUE  NOT NULL,
    path     BLOB  NOT NULL,
    vehicle_id  TEXT NOT NULL,
    begin_time  BIGINT NOT NULL,
    end_time    BIGINT NOT NULL,
    area        DOUBLE NOT NULL,
    rect        TEXT NOT NULL,
    block_id    BIGINT NOT NULL,
    layer_index TINYINT,
UNIQUE(vehicle_id,begin_time)
);

tbl pathold's DDL:
CREATE VIRTUAL TABLE IF NOT EXISTS pathway  USING geopoly
(vehicle_id   TEXT,
begin_time   BIGINT,
end_time     BIGINT,
area         DOUBLE,
rect         TEXT,
block_id     BIGINT,lyer_index  TINYINT);

(2) By Keith Medcalf (kmedcalf) on 2021-04-15 08:54:45 in reply to 1 [link]

insert "where True" (without the quotes) between the table name and the on conflict clause.  The keyword "on" following a tablename in a select statement is the introducer for a parenthetical where condition clause (as in FROM x JOIN y ON shishkabobs=delicious) and conflict(...) becomes a function call, and DO becomes a syntax error.  

You need to terminate the select statement parsing so that ON is parsed as belonging to the INSERT rather than the SELECT.

```
INSERT INTO pathway SELECT pathold._rowid_,
                           pathold._shape,
                           pathold.vehicle_id,
                           pathold.begin_time,
                           pathold.end_time,
                           pathold.area,
                           pathold.rect,
                           pathold.block_id,
                           pathold.layer_index
                      FROM pathold 
                     WHERE True
    on conflict(vehicle_id,begin_time) DO NOTHING;
```

This is a known parsing issue with INSERT ... SELECT ... ON CONFLICT ...  
<https://sqlite.org/lang_UPSERT.html>

(3) By eternal (yh2216) on 2021-04-16 03:26:16 in reply to 2

thank you very much!