How do I UPDATE a row in a table or INSERT it if it doesn't exist?

Question

I have the following table of counters:

CREATE TABLE cache (     key text PRIMARY KEY,     generation int ); 

I would like to increment one of the counters, or set it to zero if the corresponding row doesn't exist yet. Is there a way to do this without concurrency issues in standard SQL? The operation is sometimes part of a transaction, sometimes separate.

The SQL must run unmodified on SQLite, PostgreSQL and MySQL, if possible.

A search yielded several ideas which either suffer from concurrency issues, or are specific to a database:

• Try to INSERT a new row, and UPDATE if there was an error. Unfortunately, the error on INSERT aborts the current transaction.

• UPDATE the row, and if no rows were modified, INSERT a new row.

• MySQL has an ON DUPLICATE KEY UPDATE clause.

EDIT: Thanks for all the great replies. It looks like Paul is right, and there's not a single, portable way of doing this. That's quite surprising to me, as it sounds like a very basic operation.

Answer 1

MySQL (and subsequently SQLite) also support the REPLACE INTO syntax:

REPLACE INTO my_table (pk_id, col1) VALUES (5, '123'); 

This automatically identifies the primary key and finds a matching row to update, inserting a new one if none is found.

Documentation: https://dev.mysql.com/doc/refman/8.0/en/replace.html

Answer 2

SQLite supports replacing a row if it already exists:

INSERT OR REPLACE INTO [...blah...] 

You can shorten this to

REPLACE INTO [...blah...] 

This shortcut was added to be compatible with the MySQL REPLACE INTO expression.