CODEWITHSUNDEEP
mysql
Anton
Anton

Reputation: 115

MYSQL: How to select a common table expression into a temporary table

I installed the XAMPP stack and am using PHPMYADMIN and MariaDB version 10.4.11. Although I can get common table expressions and temporary tables to work separately, I can't figure out how to select a common table expression into a temp table, or find any example of this on the internet. Here is a simple code sample of what I want to do.

with recursive cte (firstValue, secondValue) AS
(select "FirstValue", "SecondValue")

CREATE TEMPORARY TABLE tmp SELECT * FROM cte;

Sample Data: [id, name, parentid, order] (99, "ten", 75, 20) (75, "one", NULL, 20) (54, "hi", 75, 10) (12, "bye", 54, 3)

Required Result: [id, name, parentid, order] (75, "one", NULL, 20) (54, "hi", 75, 10) (12, "bye", 54, 3) (99, "ten", 75, 20)

Upvotes: 0

Views: 2418

Answers (1)

Akina
Akina

Reputation: 42622

CTE is a part of SELECT query part, not a part of whole query. So

CREATE TEMPORARY TABLE tmp 
WITH RECURSIVE 
cte AS ( SELECT 'FirstValue' firstValue, 
                'SecondValue' secondValue )
SELECT * FROM cte;

fiddle

Upvotes: 2

Related Questions