CTE и UPDATE, упорядоченный по нарастанию уровней вложенности

SQL 2005, 2008.
Есть таблица с иерархическими данными. Допустим, для хранения файлов с каталогами и подкаталогами произвольной степени вложенности. Есть поле - не вычисляемое, хранимое в таблице - полный путь к файлу. Само собой, есть id и pid для создания иерархии.

create table t(
    id int primary key,
    pid int,
    name nvarchar(255),
    fullpath nvarchar(max),
    constraint FK_t_t foreign key (pid) references t (id)
)

Задача: при изменении имени каталога надо изменить поле fullpath для всех чайлдов по иерархии.

Найти всех чайлдов с помощью CTE просто, аналогично примеру D по этой ссылке: WITH common_table_expression (Transact-SQL).
Там даже есть пример I. Using a recursive CTE in an UPDATE statement, но в нем неважен порядок выполнения апдейта, он просто затрагивает чайлдов всех уровней. А для пересчета всех fullpath как раз важно, чтобы пересчет и апдейт шел поуровнево - сначала все чайлды первого уровня, потом второго и т.д.

Вопрос: можно ли такой "упорядоченный апдейт" сделать с помощью CTE?

P.S. Я понимаю, что с вычисляемым, а не хранимым полем fullpath такой вопрос бы не стоял. Но если предположить, что структуру таблицы менять нельзя, это входит в условие задачи, есть ли тогда решение?

GreenSunrise

Задача: при изменении имени каталога надо изменить поле fullpath для всех чайлдов по иерархии.

А зачем использовать для этого CTE ? Если исходно правильный fullpath уже прописан, то что мешает тупо заменить строку старого пути на новый с помощью обычного REPLACE ?

declare @t table(id int, pid int, name nvarchar(25), fullpath nvarchar(max))
insert into @t(id, pid, name, fullpath)
select 0, cast(null as int), '/', '/' union all
select 1, 0, 'folder1', '/folder1' union all
select 2, 0, 'folder2', '/folder2' union all
select 11, 1, 'folder11_renamed', '/folder1/folder11' union all
select 111, 11, 'folder111', '/folder1/folder11/folder111' union all
select 1111, 111, 'file1111', '/folder1/folder11/folder111/file1111' union all
select 112, 11, 'folder112', '/folder1/folder11/folder112' union all
select 21, 2, 'file21', '/folder2/file21'

declare @renamed_id int
set @renamed_id = 11

;with cte as (
   select c.id, p.fullpath + '/' + c.name as fullpath
     from @t as c
     join @t as p on p.id = c.pid
    where c.id = @renamed_id
   union all
   select t.id, c.fullpath + '/' + t.name as fullpath
     from @t as t
     join cte as c on c.id = t.pid
)
update t
   set t.fullpath = c.fullpath
  from @t as t
  join cte as c on c.id = t.id

select * from @t

id          pid         name                      fullpath
----------- ----------- ------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0           NULL        /                         /
1           0           folder1                   /folder1
2           0           folder2                   /folder2
11          1           folder11_renamed          /folder1/folder11_renamed
111         11          folder111                 /folder1/folder11_renamed/folder111
1111        111         file1111                  /folder1/folder11_renamed/folder111/file1111
112         11          folder112                 /folder1/folder11_renamed/folder112
21          2           file21                    /folder2/file21

(8 row(s) affected)

ChA

GreenSunrise Задача: при изменении имени каталога надо изменить поле fullpath для всех чайлдов по иерархии. А зачем использовать для этого CTE ? Если исходно правильный fullpath уже прописан, то что мешает тупо заменить строку старого пути на новый с помощью обычного REPLACE ?

C:\Temp\Download\111\222
D:\Temp\Download\111\222

как поменять C:\Temp на C:\_Temp? Собрать все подэлементы (здесь CTE требуется) и уже к ним применить REPLACE.

Senya_L

ChA GreenSunrise Задача: при изменении имени каталога надо изменить поле fullpath для всех чайлдов по иерархии. А зачем использовать для этого CTE ? Если исходно правильный fullpath уже прописан, то что мешает тупо заменить строку старого пути на новый с помощью обычного REPLACE ? C:\Temp\Download\111\222 D:\Temp\Download\111\222 как поменять C:\Temp на C:\_Temp? Собрать все подэлементы (здесь CTE требуется) и уже к ним применить REPLACE.

Или я чего то не понимаю, или... Нафиг здесь CTE ? В полном пути потомков уже содержится путь предка, который легко выловить like-ом.

ChA

Senya_L C:\Temp\Download\111\222 D:\Temp\Download\111\222 как поменять C:\Temp на C:\_Temp? Собрать все подэлементы (здесь CTE требуется) и уже к ним применить REPLACE. Или я чего то не понимаю, или... Нафиг здесь CTE ? В полном пути потомков уже содержится путь предка, который легко выловить like-ом.

Из соображений производительности, например.

Senya_L

ChA Нафиг здесь CTE ? В полном пути потомков уже содержится путь предка, который легко выловить like-ом. Из соображений производительности, например.

CTE запрос+UPDATE vs. UPDATE ? Ну разве только в очень специфических случаях.

2Паганель: вот именно в такой реализации есть ли гарантия, что проход будет идти в строгом соответствии с рекурсией? То есть сначала "дети", потом "внуки", потом "правнуки" и т.д.? Этот-то вариант и у меня на тестовых данных работал, для него и топик нечего было бы создавать. Но мой вопрос состоит именно в том, гарантирован ли именно поуровневый проход? Если правнуки будут обработаны прежде внуков, то результат получится неправильный.

2Senya_L: вы уверены, что строка поиска будет однозначна? Например,
"folder1\folder2\folder1\file1"
"folder1\folder2\file2"
"folder1\folder2\folder1\folder3\folder1\file4"
Я хочу заменить folder1 на folder1_renamed. Реплейсом у меня заменится много лишнего...

2ChA: CTE для нахождения всех потомков. Или вы знаете лучший способ для нахождения всех потомков, чем CTE? Поделитесь им :-)

...
   union all
   select t.id, c.fullpath + '/' + t.name as fullpath
     from @t as t
     join cte as c on c.id = t.pid
)
...

Выделенное я считаю гарантией
Другими словами, я считаю, что если не выполнится условие c.id = t.pid, то серверу неоткуда будет взять c.fullpath

Подтвердить свои слова подробным разбором плана я не могу,
т.к. для меня (пока) планы рекурсивных запросов сложноваты...

GreenSunrise

2Senya_L: вы уверены, что строка поиска будет однозначна? Например, "folder1\folder2\folder1\file1" "folder1\folder2\file2" "folder1\folder2\folder1\folder3\folder1\file4" Я хочу заменить folder1 на folder1_renamed. Реплейсом у меня заменится много лишнего...

Я тут погонял запрос похожий на тот, что у Паганеля, и действительно встретил такую ситуацию с переименованием не того, что требуется.
Неоднозначности можно избежать, если использовать полный пути и заканчивать слэшем
"folder1\folder2\folder1\" -> "folder1\folder2\folder1_rename\". Ну и + REPLACE.