title: mySQL: how to update table and fields dependent on values in other tables?
body: <p>I have a database with hinky data in it.  Fortunately it is consistently hinky so I should be able to run some nice SQL on it and clean it up.  Here's the story:</p>
  
  <pre><code>Table 'uc_products':
  nid   model
  1     asdf
  2     qwer
  3     ghjk
  
  Table 'uc_product_stock':
  nid   sku 
  1     asdf
  22    qwer
  34    ghjk
  </code></pre>
  
  <p>So you can see that model=sku, and nid=nid.  The table uc_products has all the <strong>correct</strong> data in it, and uc_product_stock is the one with bad data (nid's that don't match the sku's).  I want to look through uc_product_stock and for each entry, compare to uc_product:</p>
  
  <ul>
  <li>Where model==sku &amp; nid==nid : yeah!  Data is good!</li>
  <li>Where model==sku &amp; nid!=nid : boo!  Bad data, so uc_stock_product.nid should be updated to match the value in uc_product.nid</li>
  </ul>
  
  <p>[I guess a more brute force way to do this would be to match each row on SKU/model and reset each uc_product_stock.nid to match the uc_product.nid -- the principal being that the wrong ones would be corrected and the right ones would be reset to the same value and stay right.  I am open to that if you think it's the best answer, but it makes me feel a little funny.  I don't like to mess with data that is already right.]</p>
  
  <p>Thanks for any help!</p>