如何在“从选择中更新"中使用表变量?询问? [英] How to use a table variable in an "update from select" query?

问题描述

我有此表变量声明，后跟一个查询:

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;

U的定义如下:

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

当我在SQL Management Studio中针对SQL Server 2005 Express运行它时，得到以下信息:

When I run that in SQL Management Studio against SQL Server 2005 Express I get the following:

消息208，第16层，状态1，第24行

Msg 208, Level 16, State 1, Line 24

无效的对象名称'@CurrentItems'.

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?

推荐答案

您已经为@CurrentItems加上了CI的别名，因此只需使用CI:

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;

还要查看您的查询，您有类似U.UId = CU.ItemID的内容.什么是CU?您已使用CI为@CurrentItems作了别名，那么CU的目的是什么?如果这是一个错误，只需输入一个错字即可确保使用CI更改对CU的任何引用.

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.

您也不要告诉我们U是什么，我希望这是一个有效的表.

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

这篇关于如何在“从选择中更新"中使用表变量?询问?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！