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

Simulating MySQL INSERT ON DUPLICATE KEY UPDATE in SQLite: Complete Guide

Relational databases often need to update a record when it already exists, or insert a new record when it does not exist. In MySQL, this functionality can be achieved using the INSERT ... ON DUPLICATE KEY UPDATE syntax, which provides a convenient way to combine insert and update operations.

However, SQLite does not natively support this syntax. Therefore, alternative ways of achieving the same effect in SQLite must be explored.

One way is to use a SELECT combined with an INSERT or UPDATE query. This involves first executing a SELECT query to check if a record with the same unique key (e.g. IP address) exists. If it exists, perform an UPDATE query to increment the hits column. If it does not exist, an INSERT query is executed to create a new record.

Alternatively, you can combine UPDATE and INSERT queries. Here, an UPDATE query is first executed, trying to increment the hits column for a specific IP address. If successful, the record exists and the operation is complete. If the UPDATE fails (because the record does not exist), an INSERT query is executed to create a new record.

Both methods require a unique constraint (or primary key) on the relevant column (e.g. ip).

Another effective solution is to use the following statement:

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

This method involves using the INSERT OR IGNORE statement to attempt to insert a new record. The IGNORE clause prevents an error from being raised if the record already exists. The subsequent UPDATE statement attempts to increment the hits column for a specific IP address. If UPDATE is successful, it means the record was successfully updated. If it fails, it means the record does not exist and the INSERT statement created it. Note that LIKE has been changed to = to ensure an exact IP address match.

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