Sqlite - Upsert *Not* Insert or Replace

SQLite - UPSERT *not* INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE

---

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

---

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

---

GOOD: Use SQLite On conflict clause
UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: 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 the 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')
          );

SQLite UPSERT / UPDATE OR INSERT

This is a late answer. Starting from SQLIte 3.24.0, released on June 4, 2018, there is finally a support for UPSERT clause following PostgreSQL syntax.

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

Note: For those having to use a version of SQLite earlier than 3.24.0, please reference this answer below (posted by me, @MarqueIV).

However if you do have the option to upgrade, you are strongly encouraged to do so as unlike my solution, the one posted here achieves the desired behavior in a single statement. Plus you get all the other features, improvements and bug fixes that usually come with a more recent release.

SQLite INSERT OR REPLACE INTO vs. UPDATE ... WHERE

UPDATE will not do anything if the row does not exist.

Where as the INSERT OR REPLACE would insert if the row does not exist, or replace the values if it does.

SQLite - update (not replace) if exists, else insert

In SQLite you can do INSERT OR REPLACE rather than a normal INSERT if you've defined a primary key on your table. If another row in the table already exists with the primary key you're attempting to insert, it gets overwritten.

So, if you made a compound primary key on your table consisting of word1 and word2, you could do:

INSERT OR REPLACE INTO link (word1, word2, n)
SELECT 
    x.word1, x.word2, x.n + COALESCE(l.n, 0)
FROM ( SELECT '%s' AS word1, '%s' AS word2, %d AS n ) x
LEFT JOIN link l ON x.word1 = l.word1 AND x.word2 = l.word2

SQLite INSERT OR REPLACE with condition

This is what i use.

First attempt the update with your condition
then, if not updated, Perform the insert...

Check one example below. calling upsertCategory

/*
 * Update a category
 */
public long updateCategory(Category category) {

    ContentValues values = new ContentValues();
    values.put(COL_CATEGORYDESCRIPTION, category.getDescription());
    values.put(COL_CATEGORYSTATUS, category.getStatus());
    values.put(COL_CATEGORYCREATEDAT, category.getCreatedAt().getDate());
    values.put(COL_CATEGORYUPDATEDAT, category.getUpdatedAt().getDate());

    long id = db.update(TABLE_CATEGORIES, values, KEY_ID + "=" + category.getId(), null);

    return id;
}


/*
 * Update or Insert a category
 */
public long upsertCategory(Category category) {

    // update row
    long id = updateCategory(category);

    // 0 rows affected
    if (id == 0) {
        // insert row
        id = insertCategory(category);
    }

    return id;
}

How do I use UPSERT in sqlite such that created_at time is preserved?

If there is a unique constraint for the column url then the syntax for UPSERT is:

INSERT INTO urls(url, title, excerpt, created_at, updated_at) VALUES (?, ?, ?, ?, ?)
ON CONFLICT(url) DO UPDATE 
SET title = EXCLUDED.title, 
    excerpt = EXCLUDED.excerpt, 
    updated_at = EXCLUDED.updated_at;

If there is also a column count:

INSERT INTO urls(url, title, excerpt, created_at, updated_at, count) VALUES (?, ?, ?, ?, ?, 1) 
ON CONFLICT(url) DO UPDATE 
SET title = EXCLUDED.title, 
    excerpt = EXCLUDED.excerpt, 
    updated_at = EXCLUDED.updated_at,
    count = count + 1;

or if you have defined count with a default value of 1 it can be omitted from the INSERT list:

INSERT INTO urls(url, title, excerpt, created_at, updated_at) VALUES (?, ?, ?, ?, ?) 
ON CONFLICT(url) DO UPDATE 
SET title = EXCLUDED.title, 
    excerpt = EXCLUDED.excerpt, 
    updated_at = EXCLUDED.updated_at,
    count = count + 1;

Sqlite INSERT OR REPLACE is incrementing rowid

This is how INSERT OR REPLACE works.

If a violation of a unique index/constraint occurs then the existing row is deleted and the new row is inserted, so a new rowid is assigned for the new row.

If you don't want this to happen you must use UPSERT:

insert into tablename(Name, Age, CustomerId) values ('Bob', 23, 5)
on conflict(CustomerId) do update 
set Name = excluded.Name,
    Age = excluded.Age;

See the demo.

UPSERT in SQLite

SQLite has no built-in UPSERT-like statement that doesn't delete the old record.

You should check the number of changes in your program, and execute the INSERT conditionally.

However, if you really want to do this in SQL, it's possible; but you have to use the INSERT ... SELECT ... form so that you are able to insert zero records, if needed:

BEGIN;
UPDATE t SET a = 'pdf' WHERE id = 2;
INSERT INTO t(id, a) SELECT 2, 'pdf' WHERE changes() = 0;
COMMIT;

---

Related Topics