How do I update a JDBC SQLite database?

I am trying to create a database (db), using SQLite and MATLAB. However, the only way I have managed to UPDATE a db row is to delete the row and then insert a new row with the updated information. The MATLAB update function seems (from reading help) to work only for Microsoft Access. Below are the print outs of my db connection handle and the before and after tables with the code ude to attempt the update. As you can see no update was performed.

>> dbConn
dbConn = 
  connection with properties:
                  DataSource: 'SQLiteProject'
                    UserName: ''
                      Driver: 'org.sqlite.JDBC'
                         URL: 'jdbc:sqlite:C:\Users\kevi ...'
                     Message: ''
                        Type: 'JDBC Connection Object'
  Database Properties:
                  AutoCommit: 'on'
                    ReadOnly: 'off'
                LoginTimeout: 0
      MaxDatabaseConnections: 0
  Catalog and Schema Information:
              DefaultCatalog: ''
                    Catalogs: {}
                     Schemas: {}
  Database and Driver Information:
         DatabaseProductName: 'SQLite'
      DatabaseProductVersion: '3.27.2'
                  DriverName: 'SQLite JDBC'
               DriverVersion: '3.27.2.1'

>> getTable = ['SELECT * FROM ' tableName];
>> data = fetch(dbConn, getTable);
>> convertTableDates(data)
ans =
  9×9 table
     abtm      aetm      sbtm      setm     p_id     esbt     p_nbr      p_nm       p_upr
    ______    ______    ______    ______    ____    ______    _____    _________    _____
    May 14    Jan 15    May 14    Dec 99    'P1'    May 14    'W45'    'wheel'      3.25 
    Jan 15    Jun 15    May 14    Jan 15    'P1'    May 14    'W45'    'wheel'      3.25 
    Jan 15    Dec 99    May 14    Oct 14    'P1'    May 14    'W45'    'wheel'      3.25 

>> update(dbConn,tableName,'esbt',{0},{'WHERE abtm = ''P1'''})
>> getTable = ['SELECT * FROM ' tableName];
>> data = fetch(dbConn, getTable);
>> convertTableDates(data)
ans =
  9×9 table
     abtm      aetm      sbtm      setm     p_id     esbt     p_nbr      p_nm       p_upr
    ______    ______    ______    ______    ____    ______    _____    _________    _____
    May 14    Jan 15    May 14    Dec 99    'P1'    May 14    'W45'    'wheel'      3.25 
    Jan 15    Jun 15    May 14    Jan 15    'P1'    May 14    'W45'    'wheel'      3.25 
    Jan 15    Dec 99    May 14    Oct 14    'P1'    May 14    'W45'    'wheel'      3.25 

I've cropped the table for ease of reading. convertTableDates just converts the date columns in the return table from real numbers into human readbale dates.

[Kojiro Saito]

Could you write down your codes by "Insert a line of code"? The screenshots are hard to understand how you write your update command.

[Kevin Roberts]

Thanks for the advice. Updated the original post with text instead of images.

[Guillaume]

I've edited the post to make it more readable.

Answer 1

[Kojiro Saito]

Thank you for sharing the code. Your update command syntax seems to be fine.

I guess where clause might not be correct. Possibly, p_id =''P1'' is right instead of abtm = ''P1'''?

update(dbConn,tableName,'esbt',{0},{'WHERE p_id = ''P1'''})

If not, could you show the values of esbt and abtm columns as original data types (before converting from convertTableDates function)?

[Kevin Roberts]

Thanks Kojiro, that worked a treat. I do feel a bit of an idiot now for missing the blindingly obvious.

[Kojiro Saito]

Glad to hear it solved!

[seu seu]

I donnot know why the reason heppens when I use "update" in Matlab to update SQlite database.

update(conn,'Login','password',{'ADMIN'},{'WHERE username = ''admin'''})

the mistake matlab give below:

Undefined function 'update' corresponding to input parameters of type 'sqlite'

[Kojiro Saito]

Are you using JDBC driver for accessing the SQLite?

There are differences between just SQLite and SQLite via JDBC driver as described in this document. update can be used for database connection object but not for sqlite connection object. For sqlite, you can use insert or exec instead.