title: Upserting to MySQL, but with multiple columns and unique index as duplicate check?
body: <p>I saw a lot of people have asked about upserting (<a href="https://stackoverflow.com/questions/6030071/mysql-table-insert-if-not-exist-otherwise-update">this</a>, <a href="https://stackoverflow.com/questions/15383852/sql-if-exists-update-else-insert-into">this</a>, <a href="https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists">this</a>, <a href="https://stackoverflow.com/questions/14058411/update-if-exist-insert-if-not-exist">this</a>, <a href="https://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql">this</a>, <a href="https://dba.stackexchange.com/questions/99120/if-exists-then-update-else-insert/101003">this</a>, and <a href="http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/" rel="nofollow noreferrer">more</a> and even the <a href="http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html" rel="nofollow noreferrer">official doc</a>).  </p>
  
  <p><strong>However, something that is not explained well enough for newbies to understand is how to create the duplicate key using primary key or unique indexes.</strong>  </p>
  
  <p><strong>What I need</strong>:<br>
  If a <code>table1</code>'s unique combination of 3 columns (<code>attributeId, entityId, carId</code>) has a duplicate in <code>table2</code>, then update the <code>value</code> column. Else take <code>table1</code>'s row and insert it into <code>table2</code>.  </p>
  
  <p>The <code>attributeId, entityId, carId</code> combination will be unique for every row.<br>
  ie: If a row has columns as <code>1,2,5</code>, then no other row will have <code>1,2,5</code>. But another row might have <code>5,1,2</code> or <code>3,4,2</code> etc.  </p>
  
  <p>The dilemma here is about creating the unique index. Is it sufficient to just do it like this:  </p>
  
  <p><code>CREATE INDEX PIndex ON table1 (attributeId, entityId, carId);</code>  </p>
  
  <p>or is it necessary to delete all other indexes and then create this index and then run a query like this? (pseudocode below):  </p>
  
  <pre><code>    INSERT INTO table1 (attributeId, entityId, carId, value, name) 
      VALUES (table2.attributeId,table2.entityId,table2.carId,table2.value,table2.name) 
  ON DUPLICATE KEY UPDATE value=VALUES(value);
  </code></pre>
  
  <p><strong>The basic logic being:</strong><br>
  If for a row in <code>table2</code>, there is a corresponding row in <code>table1</code> with exactly the same values for attributeId, entityId and carId, then update the <code>value</code> column in <code>table1</code> with the value of the <code>value</code> column in <code>table2</code>. If there is no corresponding row, then take the row of <code>table2</code> and append it to <code>table1</code>.</p>