All geek questions in one place

SQL Server 2005: update rows in specified order (e.g. ORDER BY)?

I want to update table rows in a specific order, as with the ORDER BY clause, but SQL Server does not support the ORDER BY clause in UPDATE queries.

I checked this question which provided a good solution, but my request is a bit more complicated than the one that is listed 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, I hope that you will notice that one table ( TableA) contains a hierarchy of rows, with one row being the parent or child of any other row. Rows must be updated in order from the deepest child to the root parent. This is due to the fact that it TableA.ColAshould contain a modern concatenation of its own current value with the values ​​of its children (I understand that this query only concatenates with one child, but this is done for simplicity - the goal of the example in this question does not require more words), therefore the request should be updated from bottom to top.

The solution proposed in the question 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
);

, , , (. WHERE Child.ParentColB = Parent.ColB), , sibling .

, , .

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 , , . ( , , , ). , WHERE..IN - , , ( ) , ... , .

, , ( , , , - "?" ), " ", , - , SQL, ( ) . , .

, , .:)

?

+3
4

UPDATE , .

while/cursor (uhhgg), , , CTE , , .

+1

, ( N N + 1). - . , , , : , , , . , ( - > ). , , , .

+2

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
+1

JMTyler -

1 ColA? ?

2 ? , , . . , ColC ColA ( ?)

3 - , ? , ParentColB? ? 0, ?

,

0

Source: https://habr.com/ru/post/1749262/

More articles:


All Articles