How Can I Achieve MySQL's ON DUPLICATE KEY UPDATE Functionality in SQLite?

SQLite Upsert: Mirroring MySQL's ON DUPLICATE KEY UPDATE Functionality

MySQL's ON DUPLICATE KEY UPDATE simplifies inserting or updating rows based on unique key constraints. This single query either inserts a new row if the key is unique, or updates an existing row if a duplicate key is found. SQLite lacks this direct equivalent.

Workarounds for Upserts in SQLite

SQLite offers alternative strategies to achieve the same result:

1. SELECT then INSERT or UPDATE:

This method uses two queries:

• A SELECT query to check for the existence of a row with the given key.
• An INSERT or UPDATE query, depending on the SELECT query's outcome.

This approach requires two database interactions, potentially impacting performance.

2. UPDATE, then INSERT on Failure:

This involves a single UPDATE query with error handling:

• A successful UPDATE indicates an existing row, and the data is updated.
• An unsuccessful UPDATE (due to the row not existing) triggers an INSERT query.

While this reduces database calls to one, it necessitates additional error handling for failed INSERT attempts (e.g., due to duplicate keys).

Optimal SQLite Upsert Strategy

The most efficient and robust SQLite upsert method combines INSERT OR IGNORE and UPDATE:

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

This requires a UNIQUE or PRIMARY KEY constraint on the "ip" column. The INSERT OR IGNORE attempts insertion; if a duplicate key exists, it's silently ignored. The subsequent UPDATE then increments the hit counter for the existing row. This solution offers both efficiency (a single database roundtrip) and avoids errors from duplicate key insertions.

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