How to use table variable in query "update from select"?

Question

I have this table variable declaration followed by a query:

DECLARE @CurrentItems TABLE ( ItemId uniqueidentifier, ItemUnits int ) UPDATE U SET U.Units = U.Units + [@CurrentItems].ItemUnits FROM @CurrentItems CI INNER JOIN U ON U.UId=CI.ItemId; 

And U is defined as follows:

 CREATE TABLE [dbo].[U] ( [UId] UNIQUEIDENTIFIER UNIQUE NOT NULL, [Units] INT DEFAULT ((0)) NOT NULL ); 

When I run this in SQL Management Studio vs SQL Server 2005 Express, I get the following:

Msg 208, Level 16, State 1, Line 24

Invalid object name '@CurrentItems'.

I already looked at this and this very similar questions, but I can’t understand how to solve the problem.

What is the actual problem and how to solve it?

Answer 1

You have an alias @CurrentItems with CI , so just use CI :

 UPDATE U SET U.Units = U.Units + CI.ItemUnits FROM @CurrentItems CI INNER JOIN U ON U.UId=CI.ItemId; 

Also look at your request, you have something like U.UId = CU.ItemID . What is a CU? You made an alias for @CurrentItems with CI, so what is the purpose of CU? If this is a mistake, just a typo, be sure to change any link from CU to CI .

You also do not tell us that U , I hope this is a valid table.