title: sql update based on count
body: <p>I have a stored procedure that creates 4 temp tables and then returns records with a big select with inner joins at the end.  This is a cut-down version of the select that returns records. </p>
  
  <pre><code>SELECT R.*,
      REC.Status_Date as [Receive Status Date],
      RET.Status_Date as [Return Status Date]
  FROM    #Records R
      INNER JOIN #Temp_Receive REC ON R.ID1 = REC.ID1 
      INNER JOIN #Temp_Return RET ON R.ID1 = RET.ID1 
      INNER JOIN #Temp_Submittal SUB ON SUB.RecID = REC.ID AND SUB.RetID = RET.ID
  </code></pre>
  
  <p>Note that there may be multiple #TEMP_Receive and #TEMP_Return records for the same ID1 and the #TEMP_Submittal table is what connects them. </p>
  
  <p>I need to modify this procedure to include the number of holidays between the two status dates in two different tables that are included in the results. I can count the number of holidays from a separate table.  Something like this:</p>
  
  <pre><code>Select count(*)
  From Holiday_List
  Where Holiday_Date between REC.Status_Date and RET.Status_Date
  </code></pre>
  
  <p>I added a column Holiday_Count to the #Temp_Receive table.  How can I update the Holiday_Count column in the join with the count from the Holiday_List table?  I’ve tried something like this but from what I can find, this isn’t remotely valid SQL syntax.</p>
  
  <pre><code>UPDATE 
  (   #Records R
      INNER JOIN #TEMP_Receive REC ON R.ID1 = REC.ID1 
      INNER JOIN #TEMP_Return RET ON R.ID1 = RET.ID1 
      INNER JOIN #TEMP_Submittal SUB ON SUB.RecID = REC.ID AND SUB.RetID = RET.ID )
  SET REC.Holiday_Count = 
  ( SELECT COUNT(*) 
    FROM HOLIDAY_LIST HOL
    WHERE HOL.HOLIDAY_DATE between #TEMP_Receive.Status_Date AND #TEMP_Receive.Status_Date )
  </code></pre>
  
  <p>I’m running MS SQL Server 2012.</p>