Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: IF table exists, truncate and insert ELSE create

Tags:

mysql

Working only with MySQL (I have essentially no PHP knowledge), I need to have a table that's essentially a subset from a much larger table. The source table changes from time to time, losing some entries, gaining other new ones, and values changing for existing ones. I can describe what I want to happen, but can't seem to figure out a syntax of commands to make it work. I also know I can have two separate queries and just run whichever one I need, and I have that worked out, but I'd like to combine them if possible. Here's what I want:

IF the subset_table DOES NOT EXIST, create it as [select query], ELSE truncate the subset_table and insert [select query]

Like I said, I know there are other ways to do this - I could drop if exists/create, or I could just have two different sql files to run. I just want to know if I can do this as specified above.

Thoughts?

like image 398
John C Avatar asked Feb 15 '14 18:02

John C


People also ask

Does truncate drop and recreate table?

TRUNCATE TABLE locks the whole table to remove data from a table; thus, this command also uses less transaction space than DELETE . Unlike DELETE , TRUNCATE does not return the number of rows deleted from the table. It also resets the table auto-increment value to the starting value (usually 1).

How do you drop a table if it exists in MySQL?

DROP TABLE MySQL Command Syntax. To remove a table in MySQL, use the DROP TABLE statement. The basic syntax of the command is as follows: DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] [RESTRICT | CASCADE];

Does CREATE TABLE overwrite?

No. In general you have to DROP the table first before CREATE succeeds. There are lots of exceptions to this when it comes to actual implementations, such as if the table is temporary or permanent, access rights where multiple users can have the same table name as long as they all use different user names.


2 Answers

You can do this:

create table if not exists <tablename> . . .;

truncate table <tablename>;

insert into <tablename>(cols)
    select blah blahblah . . .;

You don't need any if statements at all.

like image 102
Gordon Linoff Avatar answered Sep 19 '22 14:09

Gordon Linoff


This can also be done through an SP (stored procedure)... makes it more readable and safe

DELIMITER $$

    DROP PROCEDURE IF EXISTS `create_table_sp`$$

    CREATE PROCEDURE `create_table_sp`()
    BEGIN

    IF NOT EXISTS (SELECT 1 FROM information_schema.TABLES WHERE table_name = '<table_name>'
    AND table_schema = DATABASE() AND table_type = 'BASE TABLE') THEN
        CREATE TABLE <subset_table_name>
        AS SELECT * FROM <main_table_name>;
    ELSE
        TRUNCATE TABLE <subset_table_name>;
        INSERT INTO <subset_table_name>
        AS SELECT * FROM <main_table_name>;
    END IF;

    END$$

    DELIMITER ;

    CALL `create_table_sp`;

DROP PROCEDURE IF EXISTS `create_table_sp`;

There is also another way,

  • You could pass the table names as arguments to the SP, in this case sub_table_name and main_table_name
  • Make the above DML statements to a string using CONCAT()
  • Create a prepared statement out of it and execute

Hope this helped....

like image 22
Jeffery Allan Avatar answered Sep 21 '22 14:09

Jeffery Allan