title: UPDATE field from table 1 to table 2 where the field joining them is not unique in table 2
body: <p>I'm trying to update a column in one table1 ('keyword') with data (from 'data') in table2 where they joined on 'itemid'. 'Itemid' is not unique in table2, however, and I'm stuck on how to get the data from the record that matches the correct 'field' to update table1, as in the tables below:</p>
  
  <p>table1:</p>
  
  <pre><code>|itemid | keywords 
  -------------------
  |   1   |      
  |   2   |      
  |   3   |      
  -------------------
  </code></pre>
  
  <p>table2:</p>
  
  <pre><code>|itemid | field     | data              
  ---------------------------------------------------------
  |   1   | author    | Shakespeare           
  |   1   | title     | Hamlet            
  |   1   | topics    | love, loyalty, treason    
  |   2   | author    | Dickens           
  |   2   | title     | Christmas Carol       
  |   2   | topics    | greed, reconciliation 
  |   3   | author    | Melville          
  |   3   | title     | Moby-Dick         
  |   3   | topics    | madness, immortality, sea 
  ---------------------------------------------------------
  </code></pre>
  
  <p>what I want to achieve is this in table1:</p>
  
  <pre><code>|itemid | keywords          
  -----------------------------------------
  |   1   | love, loyalty, treason    
  |   2   | greed, reconciliation 
  |   3   | madness, immortality, sea 
  -----------------------------------------
  </code></pre>
  
  <p>What is the right query to accomplish this? I've tried these queries, which don't throw any errors, but they don't update any lines. </p>
  
  <pre><code>UPDATE table1 AS t1, table2 AS t2 SET t1.keywords=t2.data WHERE t1.itemid=t2.itemid AND t2.field='topics'
  
  UPDATE table1 AS t1
      JOIN table2 AS t2 
      ON t1.itemid=t2.itemid
  SET t1.keywords=t2.data 
  WHERE t2.field='topics'
  
  UPDATE table1 AS t1, (SELECT itemid, data FROM table2 WHERE field='topics') AS t2 SET t1.keywords=t2.data
  </code></pre>
  
  <p>I've looked here and found lots of related UPDATE posts, but none seemed to address this issue. I'm sure it's something simple that I'm missing.</p>