title: How do I UPDATE a row in a table or INSERT it if it doesn't exist?
body: <p>I have the following table of counters:</p>
  
  <pre><code>CREATE TABLE cache (
      key text PRIMARY KEY,
      generation int
  );
  </code></pre>
  
  <p>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.</p>
  
  <p>The SQL must run unmodified on SQLite, PostgreSQL and MySQL, if possible.</p>
  
  <p>A search yielded several ideas which either suffer from concurrency issues, or are specific to a database:</p>
  
  <ul>
  <li><p>Try to <code>INSERT</code> a new row, and <code>UPDATE</code> if there was an error. Unfortunately, the error on <code>INSERT</code> aborts the current transaction.</p></li>
  <li><p><code>UPDATE</code> the row, and if no rows were modified, <code>INSERT</code> a new row.</p></li>
  <li><p>MySQL has an <code>ON DUPLICATE KEY UPDATE</code> clause.</p></li>
  </ul>
  
  <p>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.</p>