Convert MySQL Query To SQLite Query

Question

I have the following query:

INSERT INTO Test (Test1, Test2, Test3, Test4) VALUES (5, 5, 5, 55) ON duplicate KEY UPDATE Test1 = VALUES(5);

This works fine on MySQL but I get a syntax error on SQLite, my question(s) are: (1) How will I convert this query to work with SQLite whilst providing it's functionality (2) How do I update multiple field in the case of a duplicate?

Please explain in prose what that is supposed to achieve. My experience is with SQLite and I can only confirm "Does not look like known syntax." It would also be helpful to provide the `.dump` (from SQLite command line tool) of a suitably tailored toy database. I.e. something of a [mcve]. — Yunnosch, Jun 11 '17 at 07:59
Possible duplicate of [SQLite UPSERT - ON DUPLICATE KEY UPDATE](https://stackoverflow.com/questions/2717590/sqlite-upsert-on-duplicate-key-update) — hassan, Jun 11 '17 at 08:04

score 0 · Answer 1 · answered Jun 11 '17 at 07:59

It might be possible to construct a complex INSERT OR REPLACE query, but that would always delete the old row, which is often not what you want.

Anyway, SQLite is an embedded database, and designed to be used together with a 'real' programming language. Just use two separate statements:

db.execute('UPDATE Test SET Test1 = 5 WHERE ID = 42')
if db.rowcount == 0:
    db.execute('INSERT INTO Test(ID, Test1) VALUES (42, 5)')

Convert MySQL Query To SQLite Query

1 Answers1