Insert or Update in SQLite3

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

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

Comments¶

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.

%defaultDatasource jdbc:sqlite:

create table players (
    id int,
    user_name text primary key,
    age int
)

insert into players (
    id, user_name, age
) values (
    1982, 'Johnny', 23
), (
    1983, 'Steven', 29
), (
    1984, 'pepee', 40
)

select * from players

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

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