How can I create a table as another table, only if the table by the same name doesn't yet exist in a database?

Question

Error: Syntax Error at line 4

CREATE TABLE `${tempVars("id")}` AS
SELECT 'UNIXTIME', 'TODO', 'IMPORTANCE' 
FROM `${tempVars("id")}`
IF NOT EXISTS `${tempVars("id")}`;

Error: Syntax Error at line 1

IF (EXISTS (SELECT *
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'todolists'
 AND TABLE_NAME = `${tempVars("id")}`))
BEGIN
 SELECT * FROM `${tempVars("id")}`
END
ELSE
BEGIN
 CREATE TABLE `${tempVars("id")}` AS
   SELECT UNIXTIME, TODO, IMPORTANCE
   FROM `${tempVars("id")}`
END

I've tried a lot of things from existing questions, yet everything seems to error entirely. Not sure what's wrong at all, and haven't been able to fix, despite help from people much more experienced than me.

I dont think I understand the title let alone the question, if there is a question in here. — RiggsFolly, Apr 15 '20 at 17:25
The name of the table you're creating is the same as the one you're copying from. Is that really what you intended? — Barmar, Apr 15 '20 at 17:26

score 0 · Accepted Answer · answered Apr 15 '20 at 17:31

The IF NOT EXISTS option goes at the beginning of CREATE TABLE, not at the end.

CREATE TABLE IF NOT EXISTS new_table
SELECT UNIXTIME, TODO, IMPORTANCE
FROM old_table

Note also that you shouldn't put single quotes around the column names in SELECT, that will return literal strings, not the contents of the columns. Use backticks if you need to quote the column names. See When to use single quotes, double quotes, and backticks in MySQL

How can I create a table as another table, only if the table by the same name doesn't yet exist in a database?

1 Answers1