{"id":804,"date":"2025-03-05T10:35:20","date_gmt":"2025-03-05T15:35:20","guid":{"rendered":"https:\/\/waterloobae.com\/?p=804"},"modified":"2025-08-20T13:27:17","modified_gmt":"2025-08-20T17:27:17","slug":"my-sql-with-recursive-ctecommon-table-expression","status":"publish","type":"post","link":"https:\/\/waterloobae.com\/blog\/2025\/03\/05\/my-sql-with-recursive-ctecommon-table-expression\/","title":{"rendered":"[My SQL] With Recursive CTE(Common Table Expression)???"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">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<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">WITH RECURSIVE cte_name AS (\n    initial_query  -- anchor member\n    UNION ALL\n    recursive_query -- recursive member that references to the CTE name\n)\nSELECT * FROM cte_name; <\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">WITH RECURSIVE cte (n) AS\n(\n  SELECT 1\n  UNION ALL\n  SELECT n + 1 FROM cte WHERE n &lt; 5\n)\nSELECT * FROM cte;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">INSERT INTO code_master \n(unique_code, parent_code, m_code)\n(WITH RECURSIVE contestcode AS (\n    SELECT unique_code, parent_code, m_code, 1 AS level\n    FROM code_master\n    WHERE unique_code = 'contest'\n\n    UNION ALL\n\n    SELECT cm.unique_code, cm.parent_code, cm.m_code,  cc.level + 1\n    FROM code_master cm\n    INNER JOIN contestcode cc ON cm.parent_code = cc.unique_code\n)\nSELECT  \n    replace(unique_code, m_code, m_code+1),\n    parent_code,\n    m_code + 1,\nFROM contestcode where level = 3\norder by m_code desc limit 5);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The example above use level column to trace record generations(?). Level 3 means that it will select grandchildren of &#8220;contest&#8221; 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">SELECT \n  replace(gc.unique_code, gc.m_code, gc.m_code+1),    \n  gc. parent_code,      \n  gc.m_code + 1,\nFROM code_master AS parent\nJOIN code_master AS child ON child.parent_code = parent.unique_code\nJOIN psg.code_master AS gc ON gc.parent_code = child.unique_code\nWHERE parent.unique_code = 'contest'\nand parent.id &lt;= (select count(id) from code_master where parent_code = 'contest')\norder by gc.m_code desc limit 5; <\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,4],"tags":[],"class_list":["post-804","post","type-post","status-publish","format-standard","hentry","category-database","category-it-tips"],"_links":{"self":[{"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/posts\/804","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/comments?post=804"}],"version-history":[{"count":4,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/posts\/804\/revisions"}],"predecessor-version":[{"id":809,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/posts\/804\/revisions\/809"}],"wp:attachment":[{"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/media?parent=804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/categories?post=804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/tags?post=804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}