SQLiteStudio using upsert

Question

I am using Sqlite 3.32.3. I imported data into a table (rover) with SQLiteStudio 3.2.1. I want to use UPSERT to update existing rows and insert new rows. As a test, I'm using the statement below to update a single existing row. It does not work in the SQLiteStudio sql editor but it does work in the native SQLite3 terminal interface. I'm using OSX 10.14.6. Is this a limitation of SQLiteStudio or am I missing something?

INSERT INTO rover ([Employee Number], DeleteDate) VALUES ('97763','2020-06-24') ON CONFLICT ([Employee Number]) DO UPDATE SET DeleteDate = excluded.DeleteDate;

Are you sure this sqlitestudio is using the version of SQLite you think it is? — Shawn, Jun 27 '20 at 00:43
I used .version to check the version. SQLite 3.32.3 2020-06-18 14:00:33 — Michael, Jun 27 '20 at 01:54
I just downloaded DBeaver and it works in that GUI. It's a shame because I like the SQLiteStudio interface the better. — Michael, Jun 27 '20 at 02:05

score 0 · Accepted Answer · answered Jun 28 '20 at 09:24

The .version command you used was in terminal infertace and it is version used by terminal binary. SQLiteStudio in version 3.2.1 uses SQLite 3.22.0 on MacOS X, unfortunately (See menu SQLiteStudio -> About -> Environment tab).

That being said, fortunately, you can replace SQLite library file used by SQLtieStudio. Just take libsqlite3.0.dylib from your system (or if you maybe compiled your own) and replace the existin one in SQLiteStudio.app/Contents/Frameworks/. It's important that the new library has Loadable Extensions compile-time option enabled, cause SQLiteSTudio uses it.

SQLiteStudio 3.2.2 (to be released) will have up to date SQLite.

SQLiteStudio using upsert

1 Answers1