title: How to reference a table variable in sub-query of an UPDATE query in SQL Server
body: <p>I have an UPDATE query in a stored procedure, in which I need to update a table variable that has been already populated by some data. This query, as in code below, is giving an error due to referencing of table variable in sub-query i.e. <code>@daysPerEmail.ProductId</code> being referenced in sub-query.  I tried using an alias for table variable as in <code>UPDATE @daysPerEmail d SET LastEmailDate...</code>, but it did not help.</p>
  
  <p><strong>Question</strong>:  How can I re-write the query so table variable column of ProductId is correctly referenced in sub-query?</p>
  
  <pre><code>UPDATE @daysPerEmail SET LastEmailDate = (SELECT max(ActionDate) FROM 
          useractionsreport  WHERE ProductId = @daysPerEmail.ProductId 
         and ActionTypeId = @userActionTypeId );
  </code></pre>
  
  <p><strong>UPDATE 1</strong></p>
  
  <p>To reproduce this in SSMS, I use following query. SSMS gives an error saying <code>Must declare the scalar variable "@daysPerEmail".</code></p>
  
  <pre><code>DECLARE @daysPerEmail TABLE (
      VendorId INT,
      DaysPerEmail SMALLINT,
      LastEmailDate DATE,
      ProductId INT 
  )
  
  INSERT INTO @daysPerEmail (VendorId, DaysPerEmail, LastEmailDate, ProductId)
      VALUES (10, 5, NULL, 11), (12, 3, NULL, 15), (14, 1, NULL, 22);
  
  UPDATE @daysPerEmail
  SET LastEmailDate = (SELECT
      MAX(ActionDate)
  FROM UserActionsReport   
  WHERE ProductId = @daysPerEmail.ProductId
  AND ActionTypeId = 2);
  </code></pre>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>The answer by Paolo worked as expected, but I also found another simple solution using query as below. All I had to do was give a unique name to the column of ProductId in @daysPerEmail table variable. I changed the name to ProdId, which does not exist in any of the tables involved in UPDATE query. Then I didn't have to reference the table variable in sub-query since the ProdId column name is only found in @daysPerEmail table variable  among tables used in UPDATE query.</p>
  
  <pre><code> DECLARE @daysPerEmail TABLE (
      VendorId INT,
      DaysPerEmail SMALLINT,
      LastEmailDate DATE,
      ProdId INT 
  )
  
  INSERT INTO @daysPerEmail (VendorId, DaysPerEmail, LastEmailDate,ProdId)
      VALUES (78, 5, NULL, 2), (78, 3, NULL, 387), (78, 1, NULL, 295);
  
  UPDATE @daysPerEmail
  SET LastEmailDate = (SELECT
      MAX(ActionDate)
  FROM UserActionsReport   
  WHERE ProductId = ProdId
  AND ActionTypeId = 2);
  </code></pre>