Search code examples
sql-serversql-updatesql-server-2014table-variable

How to reference a table variable in sub-query of an UPDATE query in SQL Server


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. @daysPerEmail.ProductId being referenced in sub-query. I tried using an alias for table variable as in UPDATE @daysPerEmail d SET LastEmailDate..., but it did not help.

Question: How can I re-write the query so table variable column of ProductId is correctly referenced in sub-query?

UPDATE @daysPerEmail SET LastEmailDate = (SELECT max(ActionDate) FROM 
        useractionsreport  WHERE ProductId = @daysPerEmail.ProductId 
       and ActionTypeId = @userActionTypeId );

UPDATE 1

To reproduce this in SSMS, I use following query. SSMS gives an error saying Must declare the scalar variable "@daysPerEmail".

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);

UPDATE 2

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.

 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);

Solution

  • what about an update with join?
    If I remember correctly it is not standard compliant but is allowed by SQL-Server:

    UPDATE  X
    SET     LastEmailDate = Z.MaxActionDate
    FROM    @daysPerEmail AS X
            JOIN (
                SELECT  MAX(Y.ActionDate) AS MaxActionDate
                        , Y.ProductId
                FROM    UserActionsReport AS Y
                WHERE   ActionTypeId = 2
                GROUP BY Y.ProductId
            ) AS Z ON Z.ProductId = X.ProductId;