CODEWITHSUNDEEP
mysqlsqlmysql-workbench
wawawa
wawawa

Reputation: 3345

What's the equivalent syntax in MySQL for `with xxx AS ()`

In MSSQL, I have a query something like:

WITH temp AS (
SELECT
    *
FROM
  xx.xxxx
)

But WITH temp AS () isn't supported in MySQL syntax, I wonder what the equivalent syntax should be in MySQL Workbench? Thanks.

Below is a screenshot of the syntax error: enter image description here

Upvotes: 1

Views: 871

Answers (1)

Akina
Akina

Reputation: 42622

If you have MySQL 5.x and you need to adapt the query which contains CTE, then convert

WITH cte AS (cte query text)
SELECT ... 
FROM cte
...

to

SELECT ... 
FROM (cte query text) cte
...

If there is more than one CTE then perform this substitution with accuracy from latter CTE to former one (you may need to use multiple copies of some CTE subquery text - this is a norma).

Upvotes: 2

Related Questions