I have successfully created a VIEW in SQL Server as follows. But when I try the same in MySql, Error coming.


CREATE VIEW [dbo].[vw_PurchParent] as

WITH cte AS 
  SELECT a._Id, a._parentId, a._name, a._IsLedger
  FROM tbl_ChartOfAcc a
  WHERE _Id = 1
  SELECT a._Id, a._parentid, a._Name, a._IsLedger
  FROM tbl_ChartOfAcc a JOIN cte c ON a._parentId = c._id
  FROM cte where _IsLedger = 'Y'

Error in MySQL:

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 'cte AS( SELECT a._Id, a._parentId, a._name, ' at line 1

Server version: 5.6.41-84.1 - Percona Server (GPL), Release 84.1, Revision b308619


2 Answers 2


You need to use 8.0 for CTE in MySQL

  • This WITH defines a CTE, it has nothing to do with WITH CHECK OPTION. May 12, 2019 at 9:03
  • @ypercube I am aware of that - it doesn't mean that there isn't a bug etc
    – cEz
    May 12, 2019 at 9:04
  • No Luck... :( Same Error... "near '(WITH cte AS ( SELECT a._Id, a._parentId, a._name, a._IsLedger FROM tbl' at line 5" May 12, 2019 at 9:06
  • @arun as per dba.stackexchange.com/questions/237944/… - which version are you using?
    – cEz
    May 12, 2019 at 9:09
  • Ok @Cez, I'll upgrade and Inform. Thank you May 12, 2019 at 9:28

First of all, MySQL has been supporting CTEs since relatively recently, starting from version 8.0 (as has been mentioned by others). The syntax error you are getting seems to indicate that you are using an older version. Therefore, your database server requires an upgrade1.

Apart from that, there are issues with your CREATE VIEW statement that need to be addressed in order to make it work in MySQL.

  1. Square bracket delimiters.

    Use of square brackets to delimit names, like in the statement's first line:

    CREATE VIEW [dbo].[vw_PurchParent] as

    is specific to Transact-SQL. For MySQL, you need to replace them with either double quotes (") or backticks (`). Or, in this case, you can just omit them altogether, because the name contains only letters and an underscore, which are allowed to be used in names without delimiting.

  2. Schema name.

    In SQL Server, databases contain schemas, and schemas contain objects (tables, views, functions, stored procedures etc.). Therefore, the full name of an object consists of three parts:


    When it only has two parts, like in this case ([dbo].[vw_PurchParent]), then it is interpreted as


    However, MySQL does not support schemas in databases. When an object reference has two parts, it is interpreted as


    Therefore, it is likely that you will want to drop the dbo. part of the view name when adjusting the script for MySQL.

  3. Missing keyword RECURSIVE.

    The CTE in this view's definition is a recursive CTE2. MySQL supports recursive CTEs but, unlike SQL Server, it requires that the RECURSIVE keyword be specified when one or more CTEs in the WITH clause are recursive.

    Therefore, the WITH line of the definition will need to be rewritten as

  4. The GO keyword.

    The GO keyword at the end of your script is specific to the SQL Server platform, although it is not part of the Transact-SQL dialect per se. Rather it is an instruction interpreted by some of SQL Server client tools (SSMS, sqlcmd, the now deprecated osql). It indicates the end of a batch of statements. In this case, it should simply be replaced with a semicolon (;).

    Note that it is also a good practice in general to always end a statement with a semicolon, which is the standard statement delimiter in SQL.

1 Alternatively, you could also consider switching to MariaDB. MariaDB is a fork of MySQL, and as such it is compatible with MySQL in many respects, including the SQL dialect aspect. At the same time, though, MariaDB is known to often introduce advanced language features – which includes CTE, window functions and others – before MySQL does. For instance, as already mentioned, CTEs have been introduced to MySQL in version 8.0, which was released one year ago (in April 2018). In contrast, MariaDB has been supporting CTEs for about 2-3 years, as of writing this. (CTEs were first added to version 10.2 in 2016; the version became a stable release in 2017.)

2 It represents a UNION ALL query with a self-reference in the second leg, which is the specific structure that a recursive CTE is required to have.

  • 1
    And those are just the tip of the iceberg when it comes to syntax and feature differences between RDBMS vendors.
    – Rick James
    May 12, 2019 at 15:23
  • 2
    @RickJames: Absolutely. If even a single statement produces this many differences, then addressing all of them should take a good chapter of a book.
    – Andriy M
    May 12, 2019 at 15:32
  • @AndriyM Thank you... I have tried creating the view by considering the points (1,2 and 4) except the RECURSIVE command. It's understood that I have to use the latest version of MySql for this view to create. May 13, 2019 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.