title: Needing to access table from subquery in main update query
body: <p>I'm in need of a way to access a column from the inner table from a subquery, which I have included below with database/table names changed. The inner query returns all of the Item_Ids of Items that have a specific term config and belong to a specific customer. Obviously, the SET currently doesn't work as I can't access any of the rows of <code>let</code> from the main query.</p>
  
  <p>I'm fairly new to SQL and am struggling to wrap my head around how I might convert this to using joins instead of a subquery. The problem is that I need to be able to set ALL of the Grand Totals for any of the Item_Ids that come from the subquery.</p>
  
  <pre><code>UPDATE [Database].[dbo].[Items]
      SET GrandTotal = GrandTotal / let.CurrentValue
      WHERE Id IN (
              SELECT let.Item_Id
              FROM [Database].[dbo].[ItemTerms] let
              WHERE TermConfig_Id = 'TERM_CONFIG_ID'
                  AND Item_Id IN (
                      SELECT le2.Id
                      FROM [Database].[dbo].[LaneExhibits] le2
                      WHERE Customer_Id = 'CUST_ID'
                      )
              )
  </code></pre>
  
  <p><strong>EDIT: Add Sample Row from Subquery</strong></p>
  
  <p>Columns are Id, DataValueStatus, CurrentValue, PreviousValue, Item_Id, TermConfig_Id. The subquery only returns the Item_Id but that is the rest of the data.</p>
  
  <pre><code>424C8BF4-0FCB-E711-80C9-005056BA0972    1   460 NULL    2D4C8BF4-0FCB-E711-80C9-005056BA0972    B8FCE730-27BE-E711-80C9-005056BA0972
  </code></pre>