SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT)

sql mysql database sqlite upsert

INSERT OR IGNORE INTO visits VALUES ($ip, 0);UPDATE visits SET hits = hits + 1 WHERE ip LIKE $ip;

This requires the "ip" column to have a UNIQUE (or PRIMARY KEY) constraint.

sql mysql database sqlite 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;

sql mysql database sqlite upsert

I'd prefer UPDATE (+ INSERT if UPDATE fails). Less code = fewer bugs.

CodeHunter