title: MySQL function to insert record if update fails?
body: <p>Part of the system I'm working on at the moment involves a log in mysql, with counts being frequently updated.</p>
  
  <p>The data being inserted is of the format:</p>
  
  <pre><code>   date    | name | count |
  -----------+------+-------+
  2009-01-12 | alan |   5   |
  2009-01-12 | dave |   2   |
  2009-01-12 | mary |   1   |
  </code></pre>
  
  <p>This data is parsed regularly from a flat file, summarised as above in preparation for a db insert/update - the unique key on the database is the <code>(date, name)</code> pair.</p>
  
  <p>Previously, this system would check the existing table for any records for a given <code>date</code> and <code>name</code> pair before deciding on an update or insert.</p>
  
  <p>The problem we're having is, as this table grows, the response time isn't getting any better, and we want to reduce the number of queries as much as possible.</p>
  
  <p>The system was recently updated to run a <a href="http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html" rel="nofollow noreferrer"><code>INSERT ... ON DUPLICATE KEY UPDATE</code></a> query, which has reduced the number of <code>select</code>s marginally, but our common case by some distance is the <code>update</code>.</p>
  
  <p>I'm wondering if anyone knows of a mysql function that's essentially <code>INSERT ... ON DUPLICATE KEY UPDATE</code> in reverse, i.e. will try to update a row, if none match then perform the insert?</p>
  
  <p><hr />
  <strong>Edit</strong></p>
  
  <p>I didn't make it too clear above, what I would like to do when I have the record <code>('2009-01-12','alan','5')</code> for example, is:</p>
  
  <pre><code>UPDATE table SET count = count+5 WHERE date = '2009-01-12' and name = 'alan';
  </code></pre>
  
  <p>and if the above fails, insert the above data. The need to increment a counter is why <code>REPLACE</code> won't work. Replace performs a delete &amp; insert, and doesn't let you refer to the row being deleted, so <code>count = count + 5</code> wouldn't increment the previous <code>count</code> value for  by 5.</p>
  
  <p><a href="https://stackoverflow.com/questions/466356/mysql-function-to-insert-record-if-update-fails#466369">@jasoncohen</a> - the <code>INSERT ... ON DUPLICATE KEY UPDATE</code> does do the job, but I'm asking if there's a more optimal way to do this.</p>
  
  <p>Sorry for any confusion resulting from the poor original phrasing!</p>