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

Implementing "ON DUPLICATE KEY UPDATE" in SQLite

MySQL's "ON DUPLICATE KEY UPDATE" feature allows using a single query to insert or update rows based on the presence of a unique key constraint. In SQLite, this functionality is not natively available.

Alternative way to implement "UPSERT" operation

There are two main ways to achieve the same effect in SQLite:

1. SELECT (INSERT or UPDATE): This first selects the rows to be updated. If it exists, execute an UPDATE query; otherwise, execute an INSERT query.
2. UPDATE (INSERT if UPDATE fails) : This method first attempts to update the row. If the update fails (indicating that the row does not exist), an INSERT query is executed.

Preferred method

The preferred method depends on the specific use case. For the case of enforcing a unique constraint on the key column, you can use the following query:

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

This combination ensures that the operation is executed as a single atomic operation, thus maintaining data integrity.

Another solution

Another effective solution worth considering can be found at: https://www.php.cn/link/21648c94be6e9adf691ebc249fa6689c

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