Recursive SQL SELECT, UPDATE and DELETE with recursive CTE in MariaDB

cte_examples.sql

-- SELECT

WITH recursive custom_cte AS
  ( SELECT
      *
    FROM
      target_table
    WHERE
      target_table.id = "value"
      
    UNION ALL
    
    SELECT
      a.*
    FROM
      target_table a
    INNER JOIN
      custom_cte b
    ON
      a.parent = b.id
  )
SELECT
  *
FROM
  custom_cte



-- UPDATE

UPDATE
  target_table AS table_alias
JOIN
  ( SELECT
      * 
    FROM
      ( WITH recursive custom_cte AS
        ( SELECT
            *
          FROM
            target_table
          WHERE
            target_table.id = "value"
            
          UNION ALL
          
          SELECT
            a.*
          FROM
            target_table a
          INNER JOIN
            custom_cte b
          ON
            a.parent = b.id
        )
      SELECT
        *
      FROM
        custom_cte
      ) AS x
  ) AS to_update
ON
  table_alias.id = to_update.id
SET
  table_alias.column = value



-- DELETE

DELETE table_alias FROM
  target_table AS table_alias
JOIN
  ( SELECT
      * 
    FROM 
      ( WITH recursive custom_cte AS 
        ( SELECT
            *
          FROM
            target_table
          WHERE
            target_table.id = "value"
            
          UNION ALL
          
          SELECT
            a.*
          FROM
            target_table a
          INNER JOIN
            custom_cte b
          ON
            a.parent = b.id
        )
      SELECT
        *
      FROM
        custom_cte
      ) AS x
  ) AS to_update
ON
  table_alias.id = to_update.id