Want to use one 'INSERT OR REPLACE' for both create and update in SQLITE table-CodePudding

I am trying to use the INSERT OR REPLACE query for both updates and create tasks. This works if I pass r_id to the query but I want to replace only if r_id is passed or Insert if r_id is empty. When I'm trying to do this I'm getting a type mismatch error. r_id is the primary key, integer in this.

 INSERT OR REPLACE INTO ${tableNames[1].name} (r_id,r_from_ac_type_id,r_to_ac_type_id,r_date,r_amount,r_note) values`  
      recordItems
        .map(
          i =>
            `('${i.r_id}','${i.r_from_ac_type_id}','${i.r_to_ac_type_id}', '${i.r_date}','${i.r_amount}','${i.r_note}')`,
        )
        .join(',')

CodePudding user response：

You can use UPSERT. Example from that page:

INSERT INTO vocabulary(word) VALUES('jovial')
    ON CONFLICT(word) DO UPDATE SET count=count 1;

UPSERT is a budget version of MERGE, from my understanding sqlite does not yet support MERGE.

CodePudding user response：

Your answer in link.

You must use autoincrement mechanism will then generate a new ID value.