Sample Header Ad - 728x90

SQLite Upsert - syntax error

0 votes
0 answers
150 views
I'm trying to use SQLite's upsert syntax as described [in their documentation](https://www.sqlite.org/lang_UPSERT.html) However I get the following error, in both WebSql (SQLite v3.41.2) and SQLite on Android (SQLite v3.40.0)
could not prepare statement (1 near \"DO\": syntax error)
The error suggests this syntax isn't supported but UPSERT support was added in 3.24.0. I also tested the exact query I'm using on [dbFiddle](https://dbfiddle.uk/LC7RWg2g) and it runs fine, I'm lost! /edit The query was in the dbFiddle, but for clarity, I'm using [Ionic](https://ionicframework.com/) with the [SQLite plugin](https://ionicframework.com/docs/v5/native/sqlite) . Given the following table CREATE TABLE Drawings( id INTEGER NOT NULL UNIQUE, title TEXT NOT NULL, fileId TEXT NOT NULL, documentUrl TEXT, PRIMARY KEY(id)) Running the following query INSERT INTO Drawings(id, title, fileId, documentURL) VALUES(1,'asd','asd123123asd','qwefdfg') ON CONFLICT (id) DO UPDATE SET title = excluded.title, fileId = excluded.fileId, documentURL = excluded.documentUrl Yields the error
could not prepare statement (1 near \"DO\": syntax error)
Basically it seems that the ON CONFLICT DO UPDATE isn't supported but that was added in SQLite 3.24.0 and I'm testing in 3.41.2 (WebSQL) and 3.40.0 (Android API 34)
Asked by Eriedor (51 rep)
Jul 18, 2023, 10:43 PM
Last activity: Jul 22, 2023, 12:23 AM