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

xxxhomie21 0
Viewed 47

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(',')
replace sqlite react-native sql
2 Answers

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.

Lennart - Slava Ukraini0

Your answer in link.

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

RomaZakharov0
Related