在 Update 语句中使用子查询

Question

我在删除时触发的触发器中有以下 SQL 语句：

UPDATE bk2_InfoPages
SET SortOrder = SortOrder - (SELECT COUNT(*) FROM Deleted d WHERE d.SortOrder <= SortOrder)

我的问题是最后一个 SortOrder 引用了 Deleted 表，而不是 bk2_InfoPages< /代码>表。 我不允许向 bk2_InfoPages 表添加别名，因为它是一个 UPDATE 语句 - 那么我应该怎么做？

Answer 1

UPDATE bk2_InfoPages
SET SortOrder = SortOrder - (SELECT COUNT(*) FROM Deleted d WHERE d.SortOrder <= bk2_InfoPages.SortOrder)

Answer 2

这应该可行：

UPDATE b
SET SortOrder = SortOrder - 
  (SELECT COUNT(*) FROM Deleted d WHERE d.SortOrder <= b.SortOrder)
from bk2_InfoPages b

您必须为表添加别名才能执行子查询，例如：

-- this executes fine 
create table #t ( t int)

update t 
set t = (select count(*) from #t t1 where t.t = t1.t)
from #t t

Answer 3

希望这会起作用！

UPDATE bk2_InfoPages
SET SortOrder = SortOrder - subquery.c
FROM (
SELECT SortOrder, COUNT(*) AS c
FROM Deleted d
GROUP BY SortOrder
) AS subquery
WHERE subquery.SortOrder <= bk2_InfoPages.SortOrder;