/* Работаю с Hana, но хотелось бы решения на стандартном SQL, поэтому обращаюсь к форуму Oracle. */

Дано:

Есть (сохранена в таблице tab_hyer) уже построенная иерархия с колонками

rank, tree_size, parent_id, node_id, delete_flag

где
rank - номер текущей ноды (данное дерево упорядочено по rank от 1 до некоего максимального значения)
tree_size - размер под-дерева, исходящего из этого узла, то есть число всех дочерних нод первого порядка, их ноды, ноды этих нод и так далее до листьев. То есть если взять строку с rank = 4 и в ней будет стоять tree_size = 234, строки с ранками между 4+1 .... 4+234 будут всеми дочерними нодами всех порядов строки с rank = 4.
delete_flag - выставленный сторонним алгоритмом флаг, который говорит, что некоторые ноды дерева на самом деле ошибочные и должны быть удалены (такие ноды помечены delete_flag = true, остальные помечены false)

Для простоты предположить, что в иерархии одно дерево с одним корнем (rank = 1).
В дереве соотношение одни ко многим, то есть из parent может выходить несколько node, но у каждой node строго один parent.


Задача:
написать update поля delete_flag таблицы tab_hyer, который для всех дочек всех строк с delete_flag = true проставит delete_flag = true, то есть: если сторонний алгоритм пометил ноду как ошибочную, нужно пометить и всё исходящее из нее под-дерево на удаление.

Можно, но не хотелось бы, вместо update текущей таблицы создать новую таблицу с результатом апдейта.


Ограничения:
Можно использовать джойны, оконные функции, нельзя использовать pl/sql, циклы и встроенные функции работы с иерархиями типа "верну все ранки всех нод, исходящих из данной ноды, только скажи мне ее ранк".

Опционно:
вместо решения сообщить мне куда пройти, что решение, даже если оно существует, будет нечитабельно и неподдерживаемо в силу своей интеллектуальной изощренности и посоветовать отказаться от ограничений.

Иерархия уже построена - все строки специально занумерованы (те самые ранки). Отменить нужно не только ближайших потомков нужных узлов (в ближайшем потомке мы храним parent и ваша идея сработает), а вообще всех потомков этого узла, в моем случае может быть до десяти уровней глубины. А мы ведь храним только ближайшего parent в строке и не знаем, какой parent был на три (для примера) уровня выше.

Благодарю SY за решения через drivers и Elic за решение через exist (выглядит очень изящно!).

Буду разбираться с этими решениями. Надеюсь, они учитывают, что интервалы удаляемых нод могут быть включены один в другой и т.д., то есть сторонний алгоритм мог пометить ноду 345 и ноду 367 на удаление, но при этом 367-я - дочка N-того уровня 345-й, то есть она и все ее дочки будут и так удалены при удалении потомков 345-й. Я не против, если поддерево 367-й будет помечено на удаление два раза :-)

Так как я ничего не понимаю в SQL, прошу Elic рассмотреть следующую задачу

1) Есть изначальная таблица tab_hyer, заполненная простейшей иерархией ( 1<-- 2 <-- 3 <-- 4 )



2) Есть сторонний процесс, который пометил на удаление корневую и два нижележащих узла





Таким образом теперь нода rank = 4 входит в три вложенных в друг друга поддерева, которые должны быть удалены.

3) Есть написанный Elic скрипт удаления дочерних нод всех уровней для удаленных ранее сторонним процессом узлов:




4) Доказать (желательно средствами, доступными ничего не понимающему в sql человеку), что при исполнении скрипта из пункта 3 строка с rank = 4 будет проверена на условие


одни раз, а не три раза как предполагаю я.