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.
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.
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:
database.schema.object
When it only has two parts, like in this case ([dbo].[vw_PurchParent]
), then it is interpreted as
schema.object
However, MySQL does not support schemas in databases. When an object reference has two parts, it is interpreted as
database.object
Therefore, it is likely that you will want to drop the dbo.
part of the view name when adjusting the script for MySQL.
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
WITH RECURSIVE cte AS
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.