exam.sql

CREATE TABLE zero_tbl (
  block_id            VARCHAR(33) PRIMARY KEY,
  exam                VARCHAR(33),
  cat                 VARCHAR(33),
  par_cat             VARCHAR(33),
  children            TEXT
);

INSERT INTO zero_tbl VALUES
  ('block0', 'exam0', 'cat0', 'cat0', 'block2:block3'),
  ('block1', 'exam1', NULL  , 'cat1', 'block4:'),
  ('block2', NULL   , NULL  , 'cat0', NULL),
  ('block3', NULL   , NULL  , 'cat0', NULL),
  ('block4', NULL   , NULL  , 'cat1', 'block5:block6'),
  ('block5', NULL   , NULL  , 'cat1', NULL),
  ('block6', NULL   , NULL  , 'cat1', 'block7:block8'),
  ('block7', NULL   , NULL  , 'cat1', NULL),
  ('block8', NULL   , NULL  , 'cat1', 'block9:'),
  ('block9', NULL   , NULL  , 'cat1', NULL);

-- WANT result:
/*
------------------------------------------------------------------
| block_id  | exam  | cat       | par_cat   |children            |
------------------------------------------------------------------
 block0      exam0   cat0        cat0        block1:block2:block3
 block1      exam1   NULL        cat1        block4:
 block2      exam0   cat0        cat0        NULL
 block3      exam0   cat0        cat0        NULL
 block4      exam1   cat1        cat1        block5:block6
 block5      exam1   cat1        cat1        NULL
 block6      exam1   cat1        cat1        block7:block8
 block7      exam1   cat1        cat1        NULL
 block8      exam1   cat1        cat1        block9:
 block9      exam1   cat1        cat1        NULL
*/

-- BTW: I've tried a bunch of different things along these lines:
/*
WITH RECURSIVE
Q0 AS (SELECT * FROM zero_tbl T0),
Q1 AS (SELECT * FROM zero_tbl T1)
SELECT Q0.*, Q1.* FROM Q0
LEFT JOIN Q1 ON (LOCATE(Q0.block_id, Q1.children));
*/

invert_child_to_parent.sql

UPDATE zero_tbl AS T0
INNER JOIN zero_tbl ON (
  SELECT exam, cat AS parent_cat
  FROM zero_tbl T1
  WHERE exam != NULL AND T1.exam = NULL AND id != T1.id AND LOCATE(T1.block_id, T0.children)
) AS T3
USING (parent_cat)
SET T1.exam = T3.exam;