title: Update a column in table only if a condition is met for number of values for a field in other table
body: <p>I am trying to figure out how to achieve the following. (This is a simplified version that ignores/ is stripped off  non-relevant columns.)</p>
  
  <p>I have a table <strong>Item</strong> with columns</p>
  
  <pre><code>item_id   product
  </code></pre>
  
  <p>I have another table <strong>Order</strong> with columns</p>
  
  <pre><code>order_id product item_id
  </code></pre>
  
  <h2>Here is the problem statement.</h2>
  
  <p>For a given <strong>item_id</strong> in <strong>Item</strong> table, find orders that have only one distinct <strong>product</strong> associated with it in <strong>Order</strong> table.<br>
  Mind you, <strong>one distinct product</strong> could mean one or more rows for the unique combination of <strong>product</strong> and <strong>item_id</strong>. In this case, I need to take <strong>product</strong> derived from <strong>Order</strong> table and update the <strong>Item</strong> table with that value.</p>
  
  <p>So if the <strong>Order</strong> table has values</p>
  
  <p><strong>Scenario I</strong><br>
  <strong>Order</strong> Table</p>
  
  <pre><code>order_id product  item_id
  1        apple        12
  2        apple        12
  </code></pre>
  
  <p>Then I need to update <strong>product</strong> in <strong>Item</strong> table with "apple"</p>
  
  <p><strong>Scenario II</strong><br>
  However, if <strong>Order</strong> table has values</p>
  
  <pre><code>order_id product item_id
  1        apple        12
  2        orange       12
  </code></pre>
  
  <p>nothing needs to be done. <strong>product</strong> in <strong>Item</strong> table needs no update. (It is <code>null</code> by default.) . This is one time data fix. Going forward the relationship between Order and Item table will be handled by the application. In any case, product in Order table stays after conditional update to Item table.</p>
  
  <p>What is the solution for this problem?</p>