SQL Server 2005: Update rows in a specified order (like ORDER BY)?

Question

I want to update rows of a table in a specific order, like one would expect if including an ORDER BY clause, but SQL Server does not support the ORDER BY clause in UPDATE queries.

I have checked out this question which supplied a nice solution, but my query is a bit more complicated than the one specified there.

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;

So, what I'm hoping that you'll notice is that a single table (TableA) contains a hierarchy of rows, wherein one row can be the parent or child of any other row. The rows need to be updated in order from the deepest child up to the root parent. This is because TableA.ColA must contain an up-to-date concatenation of its own current value with the values of its children (I realize this query only concats with one child, but that is for the sake of simplicity - the purpose of the example in this question does not necessitate any more verbosity), therefore the query must update from the bottom up.

The solution suggested in the question I noted above is as follows:

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

The reason that I don't think I can use this solution is because I am referencing column values from the parent table inside my subquery (see WHERE Child.ParentColB = Parent.ColB), and I don't think two sibling subqueries would have access to each others' data.

So far I have only determined one way to merge that suggested solution with my current problem, and I don't think it works.

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);

The WHERE..IN subquery will not actually return a subset of the rows, it will just return the full list of IDs in the order that I want. However (I don't know for sure - please tell me if I'm wrong) I think that the WHERE..IN clause will not care about the order of IDs within the parentheses - it will just check the ID of the row it currently wants to update to see if it's in that list (which, they all are) in whatever order it is already trying to update... Which would just be a total waste of cycles, because it wouldn't change anything.

So, in conclusion, I have looked around and can't seem to figure out a way to update in a specified order (and included the reason I need to update in that order, because I am sure I would otherwise get the ever-so-useful "why?" answers) and I am now hitting up Stack Overflow to see if any of you gurus out there who know more about SQL than I do (which isn't saying much) know of an efficient way to do this. It's particularly important that I only use a single query to complete this action.

A long question, but I wanted to cover my bases and give you guys as much info to feed off of as possible. :)

Any thoughts?

Answer 1

UPDATE statements will be executed as a single query, not as a step by step result.

You need to either use a while loop/cursor (uhhgg) or maybe make use of a CTE expression view to achieve what you are trying, which gives you the recursice possibility.

Have a look at

• Using Common Table Expressions
• Recursive Queries Using Common Table Expressions

Answer 2

You cannot succeed this in one query, because your updates are correlated (ie. level N depends on the updated value of level N+1). Relational engines frown on this very explicitly because of the Halloween Problem. The query plan will go out of its way to ensure that the updates occur as if they had two stages: one in which the current state was read, and then one in which the updated state was applied. If necessary, they'll spool intermediate tables just to preserve this apparent execution order (read all->write all). Since your query, if I understand correctly, tries to break this very premise I don't see any way you'll succeed.

Answer 3

JMTyler-

1 What kind of data is in ColA? What does it look like?

2 How is/should that column be originally populated? I ask this because you would only be able to run the update once since the value in that column would be modified from a previous run. Any additional runs would just concatenate more data. Which makes me believe there is another ColC with the original value for ColA (a person's name?)

3 Will a row ever be deleted orphaning it's children? If yes what should their ParentColB then point to? NULL? Does their depth then get set to 0 so they are now at the top of the hierarchy?

If you can answer this I can give you a solution

Thanks

Answer 4

Here is a one line SQL solution. If you ever relax the requirement that it need be one update statement you can factor out some of the complexity

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