1

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);
Sunil
  • 20,653
  • 28
  • 112
  • 197
  • is this a Table Type Variable for the stored procedure??? your update statement is valid. – M.Ali Sep 02 '15 at 14:41
  • Yes. This is a table variable as in `declare @daysPerEmail (ProductId int, LastEmailDate Date)` – Sunil Sep 02 '15 at 14:43
  • If you look closer at the procedure definition, table type variables in a procedure are `READONLY` , you would need to create another table from this table type variable (`select * into #temp from @daysPerEmail`) and then update that `#temp` table. this table variable will only be READONLY. – M.Ali Sep 02 '15 at 14:45
  • It is not valid since SSMS gives an error saying `Must declare the scalar variable "@daysPerEmail"`, even though its already declared before in the stored procedure. – Sunil Sep 02 '15 at 14:45
  • can you show the procedure definition, its really difficult to tell where you going wrong without looking at the code itself. – M.Ali Sep 02 '15 at 14:47
  • Table type variables can be used with insert and update statements from what I understand. So how come you say they are read only? – Sunil Sep 02 '15 at 14:47
  • Yes they can be used in an Insert and update statement as long as the insert or update is not against that table type variable itself. It is only in stored procedures. – M.Ali Sep 02 '15 at 14:49
  • Read the restrictions section in this MSDN article [`Table-Valued Parameters`](https://msdn.microsoft.com/en-GB/library/bb510489(v=sql.105).aspx) – M.Ali Sep 02 '15 at 14:52
  • I am not using a table-valued parameter, but just a table type variable that is locally declared in a stored procedure. – Sunil Sep 02 '15 at 14:54
  • alrite ok, then show your procedure definition, cannot help you without looking at the code itself. – M.Ali Sep 02 '15 at 14:55
  • I just added under UPDATE 1 the query that I am trying to run. – Sunil Sep 02 '15 at 14:58
  • MSDN says that `Within its scope, a table variable can be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements.` I saw this at https://msdn.microsoft.com/en-us/library/ms175010.aspx – Sunil Sep 02 '15 at 15:03
  • 1
    you have a reference to a column `@daysPerEmail.ProductId` which is not present in the table declaration itself. – M.Ali Sep 02 '15 at 15:04
  • Yes, I just missed that and added that column. Even then I get the same error in SSMS. I have corrected my T-SQL code under UPDATE 1. – Sunil Sep 02 '15 at 15:08
  • what's the error message? – Paolo Sep 02 '15 at 15:13
  • @Paolo, Error message is `Must declare the scalar variable "@daysPerEmail"` – Sunil Sep 02 '15 at 15:20

2 Answers2

6

Use square brackets, like [@daysPerEmail] instead of @daysPerEmail

That way you can use the alias with table variables.

Office1440
  • 61
  • 1
  • 2
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;
Seph
  • 8,472
  • 10
  • 63
  • 94
Paolo
  • 2,224
  • 1
  • 15
  • 19
  • What is IIRC in this context? – Sunil Sep 02 '15 at 15:21
  • If I Remember Correctly – Paolo Sep 02 '15 at 15:23
  • Another solution that worked was to simply change ProductId column name to ProdId when defining the table variable @daysPerEmail. This meant that there was no need to reference the table variable in sub-query since ProdId column name was unqiue across all tables tables of UPDATE query. – Sunil Sep 02 '15 at 15:25
  • Yes, I get that now. Sounded like some big term from SQL Server. LOL. – Sunil Sep 02 '15 at 15:26