Solution for Getting a Syntax Error when Trying to Create a CTE in MySQL
is Given Below:
Here’s the code:
WITH sub_query AS (Select imdp_title_id FROM movie_ratings)
Here’s the output:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
MySQL version : ‘8.0.26’
Software Used : MySQL Workbench 8.0
Thanks in advance.
When you use CTE syntax, you must define the CTE, and then use that CTE in a DML statement.
The following example defines CTEs named cte1 and cte2 in the WITH
clause, and refers to them in the top-level SELECT that follows the
WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;
This shows that you have a final SQL statement following one or more
<cte> AS ( <subquery> ) definitions.
MySQL 8.0 supports
DELETE DML statements following the CTE’s.