title: conditional on duplicate key update
body: <p>I'm trying to insert a new row, but if the key already exists, I want to update the row ONLY if a certain other value is in the table is different. Is this possible in a mysql query/statement?</p>
  
  <p>My table consist of the following columns: hat, mittens, name, last_update</p>
  
  <p>hat+mittens make up the unique index (say the values for "hat" and "mittens" are colors)</p>
  
  <p>Let's assume this is already in the table:</p>
  
  <pre>
  1. hat=blue mittens=green name=george last_update=tuesday
  2. hat=red mittens=green name=bill last_update=monday
  </pre>
  
  <p>On a new key, I want to insert as usual. On duplicate key, I want to do an update ONLY IF the name changes, otherwise ignore. The reason for this is that I want to preserve the last_update value (timestamp).</p>
  
  <pre>
  hat=yellow mittens=purple name=jimmy -- insert new row
  hat=blue mittens=green name=george -- ignore 
  hat=blue mittens=green name=betty -- update row
  </pre>
  
  <p>Is this possible without using separate statements to first look up the existing row, compare values and then issue an update if necessary? If so, what would be the syntax?</p>
  
  <hr>
  
  <p>Thanks for your responses. I tried all of them. Indeed, using just a simple UPDATE statement like </p>
  
  <pre><code>update tbl set name='george' where hat='blue' and mittens='green'
  </code></pre>
  
  <p>results in no rows being updated. But, using either </p>
  
  <pre><code> INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name='george';
  </code></pre>
  
  <p>or </p>
  
  <pre><code>INSERT INTO tbl (hat, mittens, name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name=CASE WHEN name &lt;&gt; VALUES(name) THEN VALUES(name) ELSE name END;
  </code></pre>
  
  <p>somehow results in the row being updated (and the timestamp changed).</p>
  
  <p>FWIW, this is the table I'm using:</p>
  
  <pre><code>CREATE TABLE `tbl` (
  `hat` varchar(11) default NULL,
  `mittens` varchar(11) default NULL,
  `name` varchar(11) default NULL,
  `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `clothes` (`hat`,`mittens`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  </code></pre>
  
  <p>MySQL is version 4.1.22 (perhaps this matters?)
  Again, my appreciation for all of the replies.</p>