SQL Server 2005：按指定顺序更新行（如 ORDER BY）？

Question

我想以特定顺序更新表中的行，就像包含 ORDER BY 子句时所期望的那样，但 SQL Server 不支持 UPDATE 查询中的 ORDER BY 子句。

我已经查看了这个问题，它提供了一个很好的解决方案解决方案，但我的查询比那里指定的查询要复杂一些。

UPDATE TableA AS Parent
SET Parent.ColA = Parent.ColA + (SELECT TOP 1 Child.ColA
    FROM TableA AS Child
    WHERE Child.ParentColB = Parent.ColB
    ORDER BY Child.Priority)
ORDER BY Parent.Depth DESC;

因此，我希望您会注意到单个表 (TableA) 包含行层次结构，其中一行可以是任何其他行的父行或子行。需要按照从最深的子级到根父级的顺序更新行。这是因为 TableA.ColA 必须包含其当前值与其子项值的最新串联（我意识到此查询仅与一个子项串联，但这是为了为了简单起见 - 这个问题中示例的目的不需要更多的冗长），因此查询必须从下到上更新。

我在上面提到的问题中建议的解决方案如下：

UPDATE messages
SET status=10
WHERE ID in (SELECT TOP (10) Id
    FROM Table
    WHERE status=0
    ORDER BY priority DESC
);

我认为我不能使用此解决方案的原因是因为我在子查询中引用父表中的列值（请参阅 WHERE Child.ParentColB = Parent.ColB），并且我不认为两个同级子查询可以访问彼此的数据。

到目前为止，我只确定了一种将建议的解决方案与我当前的问题合并的方法，并且我认为它不起作用。

UPDATE TableA AS Parent
SET Parent.ColA = Parent.ColA + (SELECT TOP 1 Child.ColA
    FROM TableA AS Child
    WHERE Child.ParentColB = Parent.ColB
    ORDER BY Child.Priority)
WHERE Parent.Id IN (SELECT Id
    FROM TableA
    ORDER BY Parent.Depth DESC);

WHERE..IN 子查询实际上不会返回行的子集，它只会按照我想要的顺序返回 ID 的完整列表。但是（我不确定 - 如果我错了请告诉我）我认为 WHERE..IN 子句不会关心括号内 ID 的顺序 - 它只会检查它当前想要更新的行的 ID，看看它是否在该列表中（它们都在），无论它已经尝试更新什么顺序...这完全是浪费周期，因为它不会不改变任何东西。

所以，总而言之，我环顾四周，似乎找不到一种按指定顺序更新的方法（并包括我需要按该顺序更新的原因，因为我确信我否则会得到非常有用的“为什么？”答案），我现在正在访问 Stack Overflow，看看你们中是否有比我更了解 SQL 的专家（这并没有说太多）一种有效的方法来做到这一点。特别重要的是，我只使用单个查询来完成此操作。

这是一个很长的问题，但我想涵盖我的基础并为你们提供尽可能多的信息以供参考。 :)

有什么想法吗？

Answer 1

您无法在一个查询中成功完成此操作，因为您的更新是相关的（即，级别 N 取决于级别 N+1 的更新值）。由于万圣节问题，关系引擎对此非常明确地表示不满。查询计划将竭尽全力确保更新发生，就像它们有两个阶段一样：第一个阶段读取当前状态，第二个阶段应用更新的状态。如果有必要，他们会假脱机中间表只是为了保留这种明显的执行顺序（全部读取->全部写入）。由于您的查询，如果我理解正确的话，试图打破这个前提，我看不到您有任何成功的方法。

Answer 2

UPDATE 语句将作为单个查询执行，而不是作为逐步结果执行。

您需要使用 while 循环/游标 (uhhgg) 或者使用 CTE 表达式视图来实现您正在尝试的目标，这为您提供了递归的可能性。

查看

• 使用通用表表达式
• 使用公用表的递归查询
  表达式

Answer 3

这是一个单行 SQL 解决方案。如果您放宽了它需要是一个更新语句的要求，您可以消除一些复杂性

CREATE TABLE [TableA](
    [ID] [int] NOT NULL,
    [ParentID] [int] NULL,
    [ColA] [varchar](max) NOT NULL,
    [Priority] [varchar](50) NOT NULL,
    [Depth] [int] NOT NULL)
go

INSERT TableA
SELECT 1, NULL, 'p', 'Favorite', 0 UNION ALL
SELECT 2, 1, 'm', 'Favorite', 1 UNION ALL
SELECT 3, 1, 'o', 'Likeable', 1 UNION ALL
SELECT 4, 2, 'v', 'Favorite', 2 UNION ALL
SELECT 5, 2, 'v', 'Likeable', 2 UNION ALL
SELECT 6, 2, 'd', 'Likeable', 2 UNION ALL
SELECT 7, 6, 'c', 'Red-headed Stepchild', 3 UNION ALL
SELECT 8, 6, 's', 'Likeable', 3 UNION ALL
SELECT 9, 8, 'n', 'Favorite', 4 UNION ALL
SELECT 10, 6, 'c', 'Favorite', 3 UNION ALL
SELECT 11, 5, 'c', 'Favorite', 3 UNION ALL
SELECT 12, NULL, 'z', 'Favorite', 0 UNION ALL
SELECT 13, 3, 'e', 'Favorite', 2 UNION ALL
SELECT 14, 8, 'k', 'Likeable', 4 UNION ALL
SELECT 15,4, 'd', 'Favorite', 3

;WITH cte AS (
SELECT a.i, a.Depth, a.maxd, a.mind, a.maxc, a.di, a.ci, a.cdi, a.ID, a.y, CAST('' AS varchar(max))z
FROM(
    SELECT DISTINCT i = 1
    ,p.Depth
    ,maxd = (SELECT MAX(Depth) FROM TableA)
    ,mind = (SELECT MIN(Depth) FROM TableA)
    ,maxc = (SELECT MAX(c) FROM (SELECT COUNT(*) OVER(PARTITION BY ParentID) FROM TableA)f(c))
    ,di   = (SELECT MIN(Depth) FROM TableA)
    ,ci   = 1
    ,cdi  = (SELECT MIN(Depth) FROM TableA)
    ,p.ID
    ,CAST(p.ID AS varchar(max)) + p.ColA + SPACE(1) + CASE WHEN g IS NULL THEN '' ELSE '(' END 
                                     + ISNULL(g,'') + CASE WHEN g IS NULL THEN '' ELSE ')' END y
    FROM TableA p
    LEFT JOIN TableA c ON (c.ParentID = p.ID)
    CROSS APPLY (SELECT SPACE(1) + CAST(c2.ID AS varchar(max)) + ColA + SPACE(1) 
                 FROM TableA c2 WHERE ParentID = p.ID 
                 ORDER BY Priority 
                 FOR XML PATH(''))f(g)
    )a
UNION ALL
SELECT r.i, r.Depth, r.maxd, r.mind, r.maxc, r.di, r.ci, r.cdi, r.ID
,CASE WHEN di = cdi 
      THEN REPLACE(r.y,LEFT(r.z,CHARINDEX(SPACE(1),r.z,2)), r.z)
      ELSE r.y END [y]
,r.z
FROM(
    SELECT i = i + 1
    ,Depth
    ,[maxd]
    ,[mind]
    ,[maxc]
    ,CASE WHEN ci = maxc AND cdi = maxd
          THEN di + 1
          ELSE di
          END [di]
    ,CASE WHEN cdi = [maxd]
          THEN CASE WHEN ci + 1 > maxc
                    THEN 1
                    ELSE ci + 1
                    END
          ELSE ci
          END [ci]
    ,CASE WHEN cdi + 1 > maxd
          THEN mind
          ELSE cdi + 1
          END [cdi]
    ,id,y
    ,CAST(ISNULL((SELECT y FROM(
        SELECT p.Depth,p.ID
        ,SPACE(1) + CAST(p.ID AS varchar(max)) + p.ColA + SPACE(1) + 
        CASE WHEN g IS NULL THEN '' ELSE '(' END + ISNULL(g,'') 
      + CASE WHEN g IS NULL THEN '' ELSE ')' END y
        ,r1 = DENSE_RANK() OVER(ORDER BY p.ID) --child number
        ,r2 = ROW_NUMBER() OVER(PARTITION BY p.ID ORDER BY p.ID) --DISTINCT not allowed in recursive section
        FROM TableA p
        JOIN TableA c ON (c.ParentID = p.ID)
        CROSS APPLY (SELECT SPACE(1)+CAST(c2.ID AS varchar(max))+ColA+SPACE(1) 
                     FROM TableA c2 
                     WHERE ParentID = p.ID 
                     ORDER BY Priority 
                     FOR XML PATH(''))f(g)
        WHERE p.Depth = cdi AND cdi < di AND p.ID <> cte.ID
        )v
    WHERE r1 = ci 
    AND r2 = 1
    AND cte.y LIKE '%' + LEFT(v.y,CHARINDEX(SPACE(1),v.y,2) ) + '%'),'') AS varchar(max)) z
FROM cte
WHERE [di]<[maxd] or [ci]<[maxc] or [cdi]<[maxd]
)r
)--cte
UPDATE t
SET ColA = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
        (y,SPACE(1),''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0','')
FROM cte
JOIN TableA t ON (t.ID = cte.ID)
WHERE di = (SELECT MAX(Depth) FROM TableA)
AND cdi  = (SELECT MAX(Depth) FROM TableA)
AND ci   = (SELECT MAX(c) FROM (SELECT COUNT(*) OVER(PARTITION BY ParentID) FROM TableA)f(c)) 
OPTION(maxrecursion 0)

SELECT * FROM TableA
DROP TABLE TableA

Answer 4

JMTyler-

1 ColA 中有什么样的数据？它看起来像什么？

2 该列最初是如何/应该填充的？我问这个问题是因为您只能运行更新一次，因为该列中的值将从之前的运行中进行修改。任何额外的运行只会连接更多数据。这让我相信还有另一个 ColC 具有 ColA 的原始值（一个人的名字？）

3 一行是否会被删除，使其子项成为孤儿？如果是的话，他们的 ParentColB 应该指向什么？无效的？然后它们的深度是否设置为 0，以便它们现在位于层次结构的顶部？

如果你能回答这个问题我可以给你一个解决方案

谢谢