Advanced Search

TRUNCATE table IF EXISTS

/ Published in: MySQL
Save to your folder(s)
Truncate or Delete a table only if it exists can not be done with a single line query.
We can achieve it with dynamic querying with a stored procedure.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. CREATE PROCEDURE delete_if_exists(IN tbl CHAR(255),IN dbn char(255)) -- tbl=table_name, dbn=database_name
  2. BEGIN
  3.     IF @tbl IN (select table_name from information_schema.tables where table_schema = dbn)
  4.     THEN
  5.         SET @query = CONCAT("TRUNCATE TABLE ", @dbn, ".", @tbl);
  6.         PREPARE stmt FROM @query;
  7.         EXECUTE stmt;
  8.     END IF;
  9. END ;

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.