Updating a table variable
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.
I think the original intent was that the table in the db would be updated more frequently, but as it turns out it's only been updated once, with the initial values inserted, and subsequent changes have been datafixes to just update the columns of that sole record.Mgr Pos Num It is insisting I declare the @Current Table variable when I try to use it in the where clause.Is it simply out-of-scope or am I really doing something foolish? BTW it also didn't allow multiple SET statemetns per UPDATE statement, ie i Had to go: UPDATE @Current Table set Manager Title = Mgr.Hi I'm using the SQL 2000 table variable to hold 2 different fact sets.
I'm declaring the variable @Current Table and inserting into it using a SELECT statement with no problems.I changed the name to Prod Id, which does not exist in any of the tables involved in UPDATE query.