title: T-SQL to loop through records in one table and modify field value based on another table
body: <p>I have 2 tables. <code>TableA</code> has a series of products with costs and <code>TableB</code> has a series of multipliers based on dates. For example:</p>
  
  <p><strong>TableA</strong> (Key: Product)</p>
  
  <pre><code>Product ID     Cost   Multiplier Code
  ProductA       100    ABC
  ProductB       200    DEF
  ProductC       300    ABC
  ProductD       400    JKL
  </code></pre>
  
  <p><strong>TableB</strong> (Key: Date, Code)</p>
  
  <pre><code>Date        Code   Multiplier
  01/01/12    ABC    100
  01/01/12    DEF    200
  01/01/12    GHI    300
  01/01/12    JKL    400
  16/03/12    ABC    300
  20/06/12    ABC    900
  15/05/12    DEF    700
  </code></pre>
  
  <p><strong>Desired results:</strong></p>
  
  <p><strong>TableA</strong> (Key: Product)</p>
  
  <pre><code>Product ID     Cost      Multiplier Code
  ProductA       90000     ABC
  ProductB       140000    DEF
  ProductC       270000    ABC
  ProductD       160000    JKL
  </code></pre>
  
  <p>What I would like to do is write a T-SQL script which loops through ALL of <code>TableA</code> and at the same time, <strong><em>multiply up</em></strong> the <code>Cost</code> column using <code>TableB</code> multipliers. So in the example of <strong>ProductA</strong> above, <code>Cost</code> should become <code>100 x 900 = 90,000</code>. </p>
  
  <p>It needs to use the latest modifier based on the date in <code>TableB</code> hence using 900 as the modifier.</p>
  
  <p>Is this possible?</p>