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

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 that in SQL Management Studio against SQL Server 2005 Express I get the following:

Msg 208, Level 16, State 1, Line 24

Invalid object name '@CurrentItems'.

I've already looked through this and this very similar questions but can't figure out how to solve the problem.

What's the actual problem and how do I resolve that?

Trotline

UPDATE U SET U.Units = U.Units + CI.ItemUnits FROM @CurrentItems CI INNER JOIN U ON U.UId=CI.ItemId; works, I have tried right now – Coated

Answer 1

You've aliased @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 take a look at your query you have something like U.UId = CU.ItemID. What is CU? You've made an alias for @CurrentItems with CI, so what is the purpose of CU? If this is a mistake, just a typo make sure you change any reference to CU with CI.

You also don't tell us what U is, I hope this is a valid table.

Nicholnichola

@Trotline and you removed CU completly? – Nicholnichola

Unfortunately I used your table definitions and am not able to reproduce this, it works perfectly fine for me. – Nicholnichola

My fault - I forgot to drop the [] from the table variable name. Works without []. – Trotline

@Sharptooth are you running this in the correct database context? You should use the USE keyword and specify the database you are working on. Use MyDB... – Nicholnichola

@Trotline - you should of copy pasted my code :) you don't need [] in the table variable as it is aliased. – Nicholnichola