Updating a table variable
I just tested that in a recent version of SQL Server, and it works.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.
DECLARE @error int DECLARE @update int DECLARE @iter int SET @iter = 1 BEGIN TRAN WHILE @iter Now, the error checking is because, to ensure integrity, EACH row in the temporary table MUST make 1 update.
DECLARE @days Per Email TABLE ( Vendor Id INT, Days Per Email SMALLINT, Last Email Date DATE, Prod Id INT ) INSERT INTO @days Per Email (Vendor Id, Days Per Email, Last Email Date, Prod Id) VALUES (78, 5, NULL, 2), (78, 3, NULL, 387), (78, 1, NULL, 295); UPDATE @days Per Email SET Last Email Date = (SELECT MAX(Action Date) FROM User Actions Report WHERE Product Id = Prod Id AND Action Type Id = 2); UPDATE X SET Last Email Date = Z. Product Id; Another solution that worked was to simply change Product Id column name to Prod Id when defining the table variable @days Per Email.
Max Action Date FROM @days Per Email AS X JOIN ( SELECT MAX(Y. Product Id FROM User Actions Report AS Y WHERE Action Type Id = 2 GROUP BY Y. This meant that there was no need to reference the table variable in sub-query since Prod Id column name was unqiue across all tables tables of UPDATE query.
Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables.
In many cases a table variable can outperform a solution using a temporary table, although we will need to review the strengths and weaknesses of each in this article.