Drop in with statement-CodePudding

I'm trying to create a sql statement which has both 'with' statement and drop statement but cant seem to make it work. I have tried putting drop before as well as after 'with' statement but it always gives syntax error. Is there no way to achieve this.

with cte as (
select abc from temp1
union all
select abc from temp2)
drop table if exists TMP.dummy_table
SELECT cte.abc, tb.id, tb.name
into TMP.dummy_table
from cte
join tableEmployee tb on cte.abc = tb.abc

Query is a dummy query but shows what I'm trying to do. Can y'all please help me with this.

CodePudding user response：

WITH/SELECT is one single statement and as it is your DROP statement interrupts that creating a syntax error. Since you're using the dropped table in the query you need to move the DROP statement before the query. Make sure you have a ; at the end of the DROP or the parser will throw a syntax error when it gets to WITH.

DROP TABLE IF EXISTS TMP.dummy_table;
WITH cte as (
    SELECT abc
    FROM temp1
    UNION ALL
    SELECT abc
    FROM temp2)
SELECT cte.abc, tb.id, tb.name
INTO TMP.dummy_table
FROM cte
JOIN tableEmployee tb ON cte.abc = tb.abc;