[My SQL] With Recursive CTE(Common Table Expression)???

When you have one table for all of codes in your system, it is often to have unique_code and parent code in that table. It enables to track down hierarchical structure of codes to see who is parent or child of that record. MySQL Recursive CTE make is simple and clear to query those relationships with one SQL statement. It will looks like below in general

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name; 

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

I have one table with year information in one column and I need to create each record with new year. It can be achieved with this SQL and year is stored in m_code. The example below insert 5 records, but it can vary depending on your schema.

INSERT INTO code_master 
(unique_code, parent_code, m_code)
(WITH RECURSIVE contestcode AS (
    SELECT unique_code, parent_code, m_code, 1 AS level
    FROM code_master
    WHERE unique_code = 'contest'

    UNION ALL

    SELECT cm.unique_code, cm.parent_code, cm.m_code,  cc.level + 1
    FROM code_master cm
    INNER JOIN contestcode cc ON cm.parent_code = cc.unique_code
)
SELECT  
    replace(unique_code, m_code, m_code+1),
    parent_code,
    m_code + 1,
FROM contestcode where level = 3
order by m_code desc limit 5);

The example above use level column to trace record generations(?). Level 3 means that it will select grandchildren of “contest” records. With limit 5, it will select the most recent year 5 records. Then, it will insert 5 records with new year value. Since this SQL statement can query children or great grandchildren based on the value of level, it is very flexible to utilize.

On the other hand, it can be achieved with inner join and SQL statement can be simpler if we can sacrifice its flexibility. Select part can be replaced with below.

SELECT 
  replace(gc.unique_code, gc.m_code, gc.m_code+1),    
  gc. parent_code,      
  gc.m_code + 1,
FROM code_master AS parent
JOIN code_master AS child ON child.parent_code = parent.unique_code
JOIN psg.code_master AS gc ON gc.parent_code = child.unique_code
WHERE parent.unique_code = 'contest'
and parent.id <= (select count(id) from code_master where parent_code = 'contest')
order by gc.m_code desc limit 5;