Update 语句适用于临时表，但不适用于表值变量

Question

我定义了这个表：

CREATE TABLE #stagingtable
(
    id int identity(1,1),
    typeflag int default 0,
    resourcetype varchar(25),
    resource varchar(40),
    est int,
    planned int,
    actual int
)
And then I am looking for places where the resourcetype is not the same as the resourcetype in the previous row, so I wrote the following UPDATE:
UPDATE #stagingtable
SET typeflag = 1 
WHERE id = (
    SELECT min(id)
    FROM #stagingtable
)
OR resourcetype <> (
    SELECT resourcetype
    FROM #stagingtable rt2
    WHERE rt2.id = (
        SELECT MAX(id) 
        FROM #stagingtable rt3
        WHERE rt3.id < #stagingtable.id
    )
)

这非常有效。但是，我所处的环境不允许我使用临时表 (RDL！所以我将我的表更改为表值变量：

DECLARE @stagingtable TABLE
(
    id int identity(1,1),
    typeflag int default 0,
    resourcetype varchar(25),
    resource varchar(40),
    est int,
    planned int,
    actual int
)
But the following code doesn't work. 
UPDATE @stagingtable
SET typeflag = 1 
WHERE id = (
    SELECT min(id)
    FROM @stagingtable
)
OR resourcetype <> (
    SELECT resourcetype
    FROM @stagingtable rt2
    WHERE rt2.id = (
        SELECT MAX(id) 
        FROM @stagingtable rt3
        WHERE rt3.id < @stagingtable.id
    )
)

我收到消息：

Msg 137，级别 16，状态 1，第 431 行 必须声明标量变量 "@stagingtable"。

有没有办法更改更新语句以使其工作？

Answer 1

您的查询几乎没问题。您只需要在最后一行通过您为其提供的别名引用表变量：

WHERE rt3.id < @stagingtable.id

DECLARE @stagingtable TABLE
(
    id int identity(1,1),
    typeflag int default 0,
    resourcetype varchar(25),
    resource varchar(40),
    est int,
    planned int,
    actual int
)

UPDATE @stagingtable
SET typeflag = 1 
WHERE id = (
    SELECT min(id)
    FROM @stagingtable
)
OR resourcetype <> (
    SELECT resourcetype
    FROM @stagingtable rt2
    WHERE rt2.id = (
        SELECT MAX(id) 
        FROM @stagingtable rt3
        WHERE rt3.id < rt2.id
    )
)

Answer 2

我设法想出了正确的语法，即在变量名称周围添加方括号：

UPDATE @stagingtable
SET typeflag = 1 
WHERE id = (
    SELECT min(id)
    FROM @stagingtable
)
OR resourcetype <> (
    SELECT resourcetype
    FROM @stagingtable rt2
    WHERE rt2.id = (
        SELECT MAX(id) 
        FROM @stagingtable rt3
        WHERE rt3.id < [@stagingtable].id
    )
)