CODEWITHSUNDEEP
prestotrino
CodingNovice
CodingNovice

Reputation: 103

Does Trino (formerly Presto) INSERT work with CTEs?

I am trying to insert rows into a table with a query of this form:

with my_CTE as 
(SELECT a,b,c
FROM my_source_table
WHERE <some conditions to apply>)

INSERT INTO my_destination_table
SELECT a, b, c
FROM my_CTE;

This error appears:

mismatched input 'INSERT'. Expecting: '(', 'SELECT', 'TABLE', 'VALUES'

I know the CTE is written correctly because I can do other things with it. I know the table exists with the columns available in the CTE because I can write to it, as shown above, with Hive.

Does Trino not support CTEs and INSERT to work together as shown above?

I have reasons for wanting to use Trino instead of Hive.

Upvotes: 3

Views: 5276

Answers (1)

CodingNovice
CodingNovice

Reputation: 103

I had the order of the CTE and INSERT backwards.

This works:


INSERT INTO my_destination_table

with my_CTE as 
(SELECT a,b,c
FROM my_source_table
WHERE <some conditions to apply>)

SELECT a, b, c
FROM my_CTE;

See the previous Stack Exchange Q&A in my comment above.

Upvotes: 6

Related Questions