Alpha Sort Rock Setting Child Pages

BEGIN TRANSACTION AlphaSortPageOrder

/* Select all child pages whose grandparent has a child page with the name of "Rock Settings" */
UPDATE dbo.Page
SET [Order] = Alpha.AlphaOrder
FROM dbo.Page P
JOIN (
    SELECT Parent.PageTitle AS ParentPageTitle
        ,child.[Id]
        ,child.pageTitle
        ,RANK() OVER (
            PARTITION BY child.ParentPageId ORDER BY child.pageTitle ASC
            ) AS AlphaOrder
        ,child.[Order]
    FROM [dbo].[Page] GrandParent
    JOIN dbo.Page Parent ON Parent.ParentPageId = GrandParent.Id
    JOIN dbo.Page Child ON Child.ParentPageId = Parent.Id
    WHERE GrandParent.PageTitle = 'Rock Settings'
    ) Alpha ON Alpha.[Id] = P.[Id]

COMMIT TRANSACTION AlphaSortPageOrder