Ben Chuanlong Du's Blog

It is never too late to learn.

Insert or Update in SQLite3

Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

Comments

  1. UPSERT does NOT work with virtual table in SQLite3 currently!

The UPSERT clause (following PostgreSQL syntax) is supported in SQLite 3.24.0+.

:::sql
INSERT INTO players (
    user_name, age
) VALUES (
    'steven', 32
) ON CONFLICT (user_name) DO UPDATE
SET age=excluded.age
;

For older versions of SQLite3, you can use INSERT or REPLACE clause together with the trick of embedded subselects to keep original value of fields of the existing rows.

In [1]:
%defaultDatasource jdbc:sqlite:
In [2]:
create table players (
    id int,
    user_name text primary key,
    age int
)
In [3]:
insert into players (
    id, user_name, age
) values (
    1982, 'Johnny', 23
), (
    1983, 'Steven', 29
), (
    1984, 'pepee', 40
)
In [4]:
select * from players
In [5]:
INSERT INTO players (
    user_name, age
) VALUES (
    'Steven', 32
) ON CONFLICT (user_name) DO UPDATE 
SET age = excluded.age
;
In [6]:
select * from players

Last Inserted Row ID of a Table

The last inserted rowid of a specific table in SQLite3

References

https://stackoverflow.com/questions/15277373/sqlite-upsert-update-or-insert/15277374

https://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace

https://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update

In [ ]:

Comments