Drop table in mysql with Create statement-CodePudding

I'm facing this issue where I'm trying to drop the table if it exists but I keeping getting error. Here's what I have

DROP TABLE IF EXISTS clips_cur;
CREATE TEMPORARY TABLE clips_cur(
SELECT year_r, month_n_r, IFNULL(SUM(current_clip_count),0) as curclips
FROM data_mining.clip_summary stb 
JOIN mstr_all_offer_sum o 
JOIN mstr_all_clip_red_sum d
WHERE stb.offer_id=o.oid AND
d.offer_id = o.oid AND
clip_date>='2022-03-01'AND 
clip_date>=SUBDATE( o.st_d, INTERVAL 1 DAY) AND 
clip_date<=ADDDATE(o.end_d, INTERVAL 1 DAY) AND 
clip_date<='2022-03-28' AND
is_handraiser<>1 
group by year_r, month_n_r)

Here's the error:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TEMPORARY TABLE clips_cur( SELECT year_r, month_n_r, IFNULL(SUM(current_' at line 2

Error position: line: 1

Appreciate the help!

CodePudding user response：

Hi to create table from Query use "AS" not "()" try this:

DROP TABLE IF EXISTS clips_cur;

CREATE TEMPORARY TABLE clips_cur
AS
SELECT year_r, month_n_r, IFNULL(SUM(current_clip_count),0) as curclips
FROM data_mining.clip_summary stb 
JOIN mstr_all_offer_sum o 
JOIN mstr_all_clip_red_sum d
WHERE stb.offer_id=o.oid AND
d.offer_id = o.oid AND
clip_date>='2022-03-01'AND 
clip_date>=SUBDATE( o.st_d, INTERVAL 1 DAY) AND 
clip_date<=ADDDATE(o.end_d, INTERVAL 1 DAY) AND 
clip_date<='2022-03-28' AND
is_handraiser<>1 
group by year_r, month_n_r

CodePudding user response：

I tried and Work to me, you have error with JOIN its incomplete you need relation fields using "ON" not "WHERE"

example:

Select t1.field1,t2.field1 
From Table1 t1
Join Table2 t2 on t1.fkfield = t2.keyfield
Where t1.id = 1