MySql - Create Table If Not Exists Else Truncate?

Question

Here is the updated question:

the current query is doing something like:

$sql1 = "TRUNCATE TABLE fubar"; $sql2 = "CREATE TEMPORARY TABLE IF NOT EXISTS fubar SELECT id, name FROM barfu"; 

The first time the method containing this is run, it generates an error message on the truncate since the table doesn't exist yet.

Is my only option to do the CREATE TABLE, run the TRUNCATE TABLE, and then fill the table? (3 separate queries)

original question was:

I've been having a hard time trying to figure out if the following is possible in MySql without having to write block sql:

CREATE TABLE fubar IF NOT EXISTS ELSE TRUNCATE TABLE fubar 

If I run truncate separately before the create table, and the table doesn't exist, then I get an error message. I'm trying to eliminate that error message without having to add any more queries.

This code will be executed using PHP.

Answer 1

shmuel613, it would be better to update your original question rather than replying. It's best if there's a single place containing the complete question rather than having it spread out in a discussion.

Ben's answer is reasonable, except he seems to have a 'not' where he doesn't want one. Dropping the table only if it doesn't exist isn't quite right.

You will indeed need multiple statements. Either conditionally create then populate:

1. CREATE TEMPORARY TABLE IF NOT EXISTS fubar ( id int, name varchar(80) )
2. TRUNCATE TABLE fubar
3. INSERT INTO fubar SELECT * FROM barfu

or just drop and recreate

1. DROP TABLE IF EXISTS fubar
2. CREATE TEMPORARY TABLE fubar SELECT id, name FROM barfu

With pure SQL those are your two real classes of solutions. I like the second better.

(With a stored procedure you could reduce it to a single statement. Something like: TruncateAndPopulate(fubar) But by the time you write the code for TruncateAndPopulate() you'll spend more time than just using the SQL above.)

Answer 2

You could do the truncate after the 'create if not exists'. That way it will always exist... and always be empty at that point.

CREATE TABLE fubar IF NOT EXISTS TRUNCATE TABLE fubar