Sqlite Insert - on Duplicate Key Update (Upsert)

SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT)

Since 3.24.0 SQLite also supports upsert, so now you can simply write the following

INSERT INTO visits (ip, hits)
VALUES ('127.0.0.1', 1)
ON CONFLICT(ip) DO UPDATE SET hits = hits + 1;

On Duplicate Key not working in SQLite

INSERT .... ON DUPLICATE don't exist in SqLite. But you can use INSERT OR REPLACE to achieve the effect like the following.

INSERT 
    OR REPLACE
INTO
    text (id, text)  
VALUES
    (150574,
        (SELECT
           CASE 
              WHEN exists(SELECT 1  FROM text WHERE id=150574)
              THEN 'good' 
              ELSE 'Hello' 
           END
         )
    )

Ref: http://www.sqlite.org/lang_insert.html

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.

Simulate ON DUPLICATE KEY UPDATE in SQLITE Android

I've never tried it before, but could you first attempt to call insertWithOnConflict() with a CONFLICT_FAIL parameter and then, if it returns with a failed id code, then run an update() for the same position?

int result = insertWithOnConflict(YourDbHelperClass.tableName, null, values, SQLiteDatabase.CONFLICT_FAIL);
if (result == -1) update(tableName, values, YourDbHelperClass.rowId + "=?", new String[] { "x" }); //x being row number

Just a thought.

ON DUPLICATE KEY UPDATE with Web sql and variables

As stated in the Web SQL Database:

User agents must implement the SQL dialect supported by Sqlite 3.6.19.

So my guess is that you are going to face the same issues you get with Sqlite. It seems that SQLite UPSERT - ON DUPLICATE KEY UPDATE is not supported by Sqlite, so I suggest just trying one of the solutions provided in the answers. Maybe something like this would work:

db.transaction(function (tx) {
    tx.executeSql('INSERT OR IGNORE INTO MOVIE VALUES (?, ?)', [result.id,score]);
    tx.executeSql('UPDATE MOVIE SET rate = ? WHERE id = ?', [score,result.id]);
});

See demo

By the way, Web SQL Database is deprecated.

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;

Insert into on duplicate key query for custom update on each row

I assume that the duplicate key is the column user_id.

You can use a CASE expression:

INSERT INTO users (user_id, books) VALUES 
(1, 'book1, book2'), 
(2, 'book3, book4') 
ON DUPLICATE KEY UPDATE 
books = CASE user_id
  WHEN 1 THEN 'book10, book20'
  WHEN 2 THEN 'book30, book40'
END;

See a simplified demo.

---

Related Topics