How to use single 'INSERT OR REPLACE' for both create and update?-CodePudding

I am trying to INSERT OR REPLACE for both updates. 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 do this I'm getting a type mismatch error. r_id is the primary key, an integer.

 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.

Off topic, but be careful when injecting values into sql queries. If possible use prepared statements instead.

CodePudding user response：

Your answer in link.

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