0

I have tried to run this request on my database:

INSERT INTO scnScenarios (id,version,description,author,created_at,updated_at,tailNumber,flightLegCounter,name ) 
VALUES ('98','','','','2014-05-21 22:04:05','','9001','12','bolo') ON DUPLICATE KEY UPDATE ID=last_insert_rowid() , description='',author='';

Returns this error:

SQLiteManager: Likely SQL syntax error: INSERT INTO scnScenarios (id,version,description,author,created_at,updated_at,tailNumber,flightLegCounter,name ) 
VALUES ('98','','','','2014-05-21 22:04:05','','9001','12','bolo') ON DUPLICATE KEY UPDATE ID=last_insert_rowid() , description='',author=''; [ near "ON": syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]
CL.
  • 173,858
  • 17
  • 217
  • 259
Bolo
  • 2,668
  • 4
  • 27
  • 34

1 Answers1

0

SQLite's INSERT statement has no ON DUPLICATE KEY clause.

The easiest way to handle this is with multiple statements: try the UPDATE first, and if the record does not exist (if the number of affected records is zero), execute the INSERT.

See also SQLite - UPSERT not INSERT or REPLACE.

Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259