How to Efficiently Emulate MySQL's ON DUPLICATE KEY UPDATE in SQLite?

Simulating MySQL’s ON DUPLICATE KEY UPDATE in SQLite

SQLite lacks a direct equivalent of MySQL's ON DUPLICATE KEY UPDATE clause, which enables efficient updates and inserts of records with predefined unique constraints. However, a similar effect can be achieved through alternative methods.

Alternative methods

In order to simulate an upsert in SQLite, there are two main methods you can use:

1. SELECT (INSERT or UPDATE):

   • Execute a SELECT query to check if the record exists.
   • If the record exists, execute an UPDATE query to increment the hits column.
   • If the record does not exist, execute an INSERT query to create the record.

2. UPDATE (INSERT if UPDATE fails):

   • Try executing an UPDATE query to increment the hits column of an existing record.
   • If the UPDATE query fails because the record does not exist, execute an INSERT query to create the record.

Recommended method

Both methods have their pros and cons, but the second method (using UPDATE (or INSERT if UPDATE fails)) is generally more efficient. It requires only a single database operation and avoids unnecessary SELECT queries.

Suggested code snippets

Here is a suggested code snippet to implement the second approach using a unique constraint on the "ip" column:

<code class="language-sql">INSERT OR IGNORE INTO visits VALUES ($ip, 0);
UPDATE visits SET hits = hits + 1 WHERE ip LIKE $ip;</code>

Alternatively, the following code snippet from another Stack Overflow solution provides another working upsert implementation:

<code class="language-sql">INSERT INTO visits (ip, hits)
VALUES ($ip, COALESCE((SELECT hits FROM visits WHERE ip LIKE $ip), 0) + 1);</code>

The above is the detailed content of How to Efficiently Emulate MySQL's ON DUPLICATE KEY UPDATE in SQLite?. For more information, please follow other related articles on the PHP Chinese website!