update_perms_groupid_recursive.sql

set @newGroupId := 16;
set @rootPageId := 1041;
set @maxDepth := 99;

UPDATE pages SET perms_groupid = @newGroupId WHERE uid IN (
with recursive children as (
    select root.*, 1 as depth
    from pages root
    where root.uid = @rootPageId
       or (root.sys_language_uid > 0 and root.l10n_parent = @rootPageId)
      and root.deleted = 0
    union all
    select parent.*, child.depth + 1 as depth
    from pages parent
    inner join children child
    on (child.sys_language_uid = 0 and parent.pid = child.uid)
    where parent.deleted = 0
      and child.depth < @maxDepth
)
select uid from children
);