Getting a Syntax Error when Trying to Create a CTE in MySQL

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.

https://dev.mysql.com/doc/refman/8.0/en/with.html says:

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 clause:

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 SELECT, UPDATE, or DELETE DML statements following the CTE’s.