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 [source]
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] [source]
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 [link] [source]
thank you very much!