title: Update Table based of another with one to many relationship. No cursors
body: <p><img src="https://i.stack.imgur.com/Dsvw6.png" alt="Table Example"></p>
  
  <p>I have these two tables and some example data that is in SQL Server. </p>
  
  <p>The <code>Rules</code> table is just a <code>SELECT DISTINCT (DataSource_Id, LocationCode, WarehouseCode, WarehouseName, MTO_Regional)</code> of the <code>Inventory</code> table. </p>
  
  <p>I want to update the <code>SS_Value</code> column in the <code>Rules</code> table based on the <code>MTO_MTS</code> value in <code>Inventory</code> where/if there is a value in any of the three 'SSIn...' columns. </p>
  
  <p><strong>Example:</strong> The 1st record in the Rules table would grab the first MTO_MTS value in the Inventory table that matches up based on (DataSource_Id, LocationCode, WarehouseCode, WarehouseName, MTO_MTSRegional) and has one 'SSIn...' column that is not 0. </p>
  
  <p>So it could grab either of the first two records in Inventory since those 5 columns match, and at least one of the SSIn.. columns is a non-zero</p>
  
  <p><strong>What I've tried so far:</strong></p>
  
  <p>I have tried using a cursor but there are millions of records so can't handle it. I'm trying to construct a update statement but can't figure out the syntax. </p>
  
  <pre><code>UPDATE Rules r
  SET SS_Value = (SELECT TOP(1) MTO_MTS FROM Inventory ip
                  WHERE r.DataSource_Id = ip.DataSource_Id
                  AND r.LocationCode = ip.LocationCode 
                  AND r.WarehouseCode = ip.WarehouseCode
                  AND r.WarehouseName = ip.WarehouseName
                  AND r.MTO_MTSRegional = ip.MTO_MTSRegional 
                  (ip.SafetyStockInKLG &lt;&gt; 0 OR ip.SafetyStockInLTR &lt;&gt; 0 OR ip.SafetyStockInUnits &lt;&gt; 0)
  </code></pre>
  
  <p>Something in this general idea I thought might work, but I can't get the syntax right. </p>
  
  <p>I also tried the syntax like: </p>
  
  <pre><code>UPDATE Rules
  SET ip.SS_Value
  FROM InventoryProduction ip 
  INNER JOIN Rules
  </code></pre>
  
  <p>I run into trouble with the scope of the tables in the queries, and getting the values from the row that is being updated to apply to the WHERE clauses. </p>