SQLite insert or update statement example

Hello Friends 👋,

Welcome To Infinitbility! ❤️

For most of Crud and migration query time, we try to make the same query for insert or update like if we have the same row then update else insert a new row.

SQLite also provides multiple options to write insert or update statements in a single query.

Let start today’s tutorial SQLite insert or update statement example

First we should go with new SQLite feature SQLite upserts

SQLite upserts

In SQLite upserts, we are able to write conditions if column values match then run update query else insert.

Something like the below example.

INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
  ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;

One thing I don’t like, we have to write columns multiple times.

We have also a second option and it’s the INSERT OR REPLACE query.

SQLite INSERT OR REPLACE

SQLite REPLACE will delete the existing one and add a new row in the table then use this very carefully and provide all column details while in updating case else other data will vanish.

INSERT OR REPLACE INTO phonebook (id, name,phonenumber)
VALUES(1, 'Alice','704-555-1212');

Thanks for reading…