title: MySQL UPSERT without ON DUPLICATE KEY
body: <p>I want a <code>UPSERT</code> (<code>UPDATE</code> if exists, else insert) on a MySQL table with these fields:</p>
  
  <pre><code>CREATE TABLE item (
    uid int(11) NOT NULL AUTO_INCREMENT,
    timestamp int(11) NOT NULL DEFAULT '0',
    category1 int(11) NOT NULL DEFAULT '0',
    category2 int(11) NOT NULL DEFAULT '0',
    counter int(11) NOT NULL DEFAULT '1',
    PRIMARY KEY (`uid`)
  )
  </code></pre>
  
  <p>This table is already in productive use, and the combination of <code>(timestamp,category1,category2)</code> is for my <code>INSERT</code> unique, but not for the whole table content. That's the reason why I can't use the "<strong>ON DUPLICATE KEY</strong>" feature. </p>
  
  <p>For clarification about the UNIQUE problem:
  If category1=9 then this is my insert, and <code>(timestamp,category1,category2)</code> is unique. But the table is in productive use, and if category1=1,2 then <code>(timestamp,category1,category2)</code> is not unique. 
  As far as I know I can't add a <code>UNIQUE</code> key, as it would create problems with the categories 1+2. </p>
  
  <p>What I need is something like this:</p>
  
  <pre><code> IF (timestamp,category1,category2) exists 
       counter=existingCount + newCount
   ELSE 
       insert new record
  </code></pre>
  
  <p>I use PHP and MySQL, so any combination of both languages is fine. </p>
  
  <p>What I tried already, but takes too much time is:</p>
  
  <ul>
  <li>Single PHP <code>SELECT</code> statement, then <code>UPDATE</code> or <code>INSERT</code> </li>
  <li><code>INSERT</code> everything then <code>UPDATE</code> and <code>DELETE</code> duplicate rows </li>
  </ul>
  
  <p>Thanks to answers I created now this Stored Procedure, syntax is OK but procedure is not working:</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS myUpsert;
  DELIMITER |
  CREATE PROCEDURE myUpsert (IN v_timestamp INT, IN v_category1 INT, IN v_category2 INT, IN v_counter INT)        
  BEGIN
    DECLARE existingCounter INT;
    SELECT COUNT(counter) AS existingCounter from item 
    WHERE timestamp =v_timestamp  AND category1=v_category1 AND category2=v_category2;
  
    IF existingCounter=0 THEN
      INSERT INTO item (timestamp,category1,category2,counter) 
      VALUES (v_timestamp,v_category1,v_category2,v_counter);
    ELSE
      UPDATE item SET count=v_counter+existingCounter
      WHERE timestamp=v_timestamp AND category1=v_category1 AND category2=v_category2;
    END IF;
  END
  |
  DELIMITER ;
  </code></pre>