TRUNCATE table only if it exists (to avoid errors)

Question

I have a .sql file that clear cached data in a mysql schema before export because the cached data in these tables is useless when I import the export file to a different server. I use this script on multiple sites, but in some instances certain table don't exist because they are not required. How do I only truncate a table in MySQL if only that table exist?

TRUNCATE accesslog;
TRUNCATE cache;
TRUNCATE cache_block;
TRUNCATE cache_bootstrap;
TRUNCATE cache_customfilter;
TRUNCATE cache_field;
TRUNCATE cache_filter;
TRUNCATE cache_form;
TRUNCATE cache_image;
TRUNCATE cache_menu;
TRUNCATE cache_metatag;
TRUNCATE cache_page;
TRUNCATE cache_path;
TRUNCATE cache_token;
TRUNCATE cache_update;
TRUNCATE cache_views;
TRUNCATE cache_views_data;
TRUNCATE watchdog;

Thanks in advance.

Either (1) live with the errors (2) create those tables everywhere, possible even in this .sql (create if not exists...) (3) write an little application that checks before truncate. — Wrikken, Aug 19 '14 at 23:28
@Wrikken - cool. I thought you were being rude. But I see what you mean now. — Preet Sangha, Aug 19 '14 at 23:37
@PreetSangha: well, you put me on a path, for which I thank you. I've commented on your answer what alterations need to be done to make it actually work. — Wrikken, Aug 19 '14 at 23:42

score 12 · Answer 1 · edited May 23 '17 at 10:29

12

See this answer on Using the IF Statement to do a conditional insert:

Can you do the same using the ANSI INFORMATION_SCHEMA

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'accesslog')
    TRUNCATE accesslog

edited May 23 '17 at 10:29

Community

1
1

answered Aug 19 '14 at 23:27

Preet Sangha

64,563
18
145
216

7

Don't omit that (a) this needs to be in a function or procedure (b) you need a THEN & END IF (c) CALL the procedure and (d) DROP the procedure. – Wrikken Aug 19 '14 at 23:40
17

and if you have a second database with a table called `accesslog`, you're gonna have a bad time. – NDM May 19 '15 at 20:44
This is not SQL but from other comment "Stored program" – pdem May 30 '23 at 08:36

score 3 · Answer 2 · edited Jul 14 '21 at 10:19

3

Example table name is Salaries: before TRUNCATE u can check records(select * from Salaries) After truncate it will reset identity too.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Salaries')

begin

    TRUNCATE table Salaries;

end

edited Jul 14 '21 at 10:19

Slava Rozhnev

9,510
6
23
39

answered Jul 14 '21 at 09:54

ArulKumar

39
5

score 1 · Answer 3 · answered Nov 08 '19 at 05:18

You can try this stored procedure as it is really difficult without it.

CREATE PROCEDURE tbl_delete(IN table_name CHAR(255),IN database_name CHAR(255))
BEGIN
    IF @table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = database_name)
    THEN
        SET @query = CONCAT("TRUNCATE TABLE ", @database_name, ".", @table_name);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
    END IF;
END ;

TRUNCATE table only if it exists (to avoid errors)

3 Answers3