H2 2.x CTE support

[Yury Shafran]

Hello, everyone!

I have a query that uses several CTEs followed by an INSERT statement. The behaviour differs across H2 versions.

1.4.200 - The query works fine.
2.2.224 - Some of the CTEs give empty results, so the final result is incorrect.
2.2.232 - org.h2.jdbc.JdbcSQLSyntaxException: Syntax error in SQL statement "with ... <the last CTE>)[*]insert into ..."; expected ",, (, SELECT, TABLE, VALUES

I couldn’t find in the documentation whether INSERT, UPDATE, or DELETE are supposed to support CTEs. Could someone clarify the status of this feature?

Thanks in advance!

[Andreas Reichel]

Greetings,

since the re-implementation, the `WITH ...` belongs to the `SELECT` query only and strictly.

So instead of

WITH ...

INSERT INTO ...

SELECT ... FROM ..

You would need to write

INSERT INTO ...

WITH ...

SELECT ... FROM ..

Best and cheers

Andreas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/h2-database/ee024683-790d-4411-91b4-c13f64ffa3fdn%40googlegroups.com.