how to use upsert in react native sqlite database

Question

I have a table in react native SQLite with 2 columns app_name,key

I want to update a value if it exists based on app_name else insert new data I tried the following

 db.transaction(function (tx) {
              tx.executeSql(
                'Insert or Ignore Into client_registration (app_name, key) values(?,?); UPDATE client_registration SET app_name=?, key=? where changes() = 0 and "app_name" = ?;',
                ['coin', 123],
                (tx, results) => {
                  if (results.rowsAffected > 0) {
                   console.log('success')
                    );
                  } else console.log('Registration Failed');
                },
              );
            }); 

i am unable to ge any output .

Answer 1

SQLite supports UPSERT with the ON CONFLICT clause:

INSERT INTO client_registration(app_name, key) VALUES (?, ?)
ON CONFLICT(app_name) DO UPDATE
SET key = EXCLUDED.key;

I assume that for app_name there is a unique constrain defined in the table.

You will have to check the version of SQLite that your android API level uses, because UPSERT was introduced in version 3.24.0

Without the use of UPSERT you will have to execute 2 statements in this order:

UPDATE client_registration 
SET key=? 
WHERE app_name = ?;

INSERT OR IGNORE INTO client_registration(app_name, key) VALUES (?, ?);