3

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.

1

1 Answer 1

6

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.

1
  • Great job answering your question. Go ahead and apply the green check mark to your answer! :) Commented Dec 2, 2021 at 17:24

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.