Upsert in SQLite

upsert.sql

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

-- Approach #1: Select original values before insertion.
-- demo table 'emplyee'
CREATE TABLE employee (
    id INTEGER PRIMARY KEY,
    role TEXT,
    name TEXT);
    
-- This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be default (NULL).
INSERT OR REPLACE INTO employee (id, role, name) 
VALUES (1, 'code monkey', (SELECT name FROM Employee WHERE id = 1));
          
-- This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.
INSERT OR REPLACE INTO employee (id, name, role) 
VALUES (1, 'Susan Bar', COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer'));
          
-- Approach #2: Joining the inserting value and the original rows.
-- However, the above two method gets cumbersome when we have lot of rows.
-- demo table 'page'
CREATE TABLE page (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    title TEXT,
    content TEXT,
    author INTEGER NOT NULL REFERENCES user (id),
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
INSERT OR REPLACE INTO page (id, name, title, content, author)
SELECT old.id, new.name, new.title, old.content, new.author
FROM ( SELECT
    "about"           AS name,
    "About this site" AS title,
    42                AS author
) AS new
LEFT JOIN (
    SELECT id, name, content
    FROM page
) AS old ON new.name = old.name;

-- Approach #3: two statements with change() function
insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) 
values(<primaryKeyValue>, <value1>, <value2>, ...); 

update <table> 
set <column1>=<value1>, <column2>=<value2>, ... 
where changes()=0 
    and <primaryKey>=<primaryKeyValue>;

-- For bonus points you can append this line which returns you the id of the row whether it be a newly inserted row or an existing row.
select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

/* end */