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

User · Answer

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.

User · Answer

In PostgreSQL there is no merge command  and actually writing it is not trivial - there are actually strange edge cases that make the task  interesting    The best  as in  working in the most possible conditions  approach  is to use function - such as one shown in manual  merge db    If you don t want to use function  you can usually get away with   updated   db execute UPDATE     RETURNING 1  if   updated    db execute INSERT       Just remember that it is not fault proof and it will fail eventually

User · Answer

If you don t have a common way to atomically update or insert  e g   via a transaction  then you can fallback to another locking scheme   A 0-byte file  system mutex  named pipe  etc

User · Answer

I don t know that you are going to find a platform-neutral solution   This is commonly called an  UPSERT     See some related discussions     Solutions for INSERT OR UPDATE on SQL Server SQL Server 2005 implementation of MySQL REPLACE INTO

User · Answer

I would do something like the following   INSERT INTO cache VALUES  key  generation  ON DUPLICATE KEY UPDATE  key   key  generation   generation   1     Setting the generation value to 0 in code or in the sql but the using the ON DUP    to increment the value   I think that s the syntax anyway

User · Answer

If you re OK with using a library that writes the SQL for you  then you can use Upsert  currently Ruby and Python only    Pet upsert   name   gt   Jerry     breed   gt   beagle   Pet upsert   name   gt   Jerry     color   gt   brown     That works across MySQL  Postgres  and SQLite3   It writes a stored procedure or user-defined function  UDF  in MySQL and Postgres  It uses INSERT OR REPLACE in SQLite3

User · Answer

the ON DUPLICATE KEY UPDATE clause is the best solution because  REPLACE does a DELETE followed by an INSERT so for an ever so slight period the record is removed creating the ever so slight possibility that a query could come back having skipped that if the page was viewed during the REPLACE query   I prefer INSERT     ON DUPLICATE UPDATE     for that reason   jmoz s solution is the best  though I prefer the SET syntax to the parentheses  INSERT INTO cache  SET key    key   generation    generation  ON DUPLICATE KEY  UPDATE key    key   generation    generation   1

User · Answer

Could you use an insert trigger  If it fails  do an update

User · Answer

Standard SQL provides the MERGE statement for this task   Not all DBMS support the MERGE statement

User · Answer

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

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

Examples related to mysql

Examples related to sql

Examples related to sqlite

Examples related to postgresql

Examples related to upsert