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