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