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!