5

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?

Community
  • 1
  • 1
sharptooth
  • 167,383
  • 100
  • 513
  • 979
  • `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 – chopikadze Dec 16 '11 at 14:24

1 Answers1

9

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.

JonH
  • 32,732
  • 12
  • 87
  • 145
  • @sharptooth and you removed `CU` completly? – JonH Dec 16 '11 at 14:17
  • Unfortunately I used your table definitions and am not able to reproduce this, it works perfectly fine for me. – JonH Dec 16 '11 at 14:25
  • 2
    My fault - I forgot to drop the `[]` from the table variable name. Works without `[]`. – sharptooth Dec 16 '11 at 14:29
  • @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...` – JonH Dec 16 '11 at 14:29
  • @sharptooth - you should of copy pasted my code :) you don't need `[]` in the table variable as it is aliased. – JonH Dec 16 '11 at 14:29