How Can I truncate Multiple Tables in MySql?

Question

I need to clear all my inventory tables.

I've tryed

SELECT 'TRUNCATE TABLE ' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'

but I get this error:

Truncated incorrect DOUBLE value: 'TRUNCATE TABLE ' Error Code 1292

if this is the correct way, then what am I doing wrong?

Answer 1

SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'

You should then take every row of the result set and call it as the SQL statement. You can do it by coping and pasting the separated results to the command window.

But much better solution is to write some program which will make the query above and loop through the results and make every query.

Use PHP for that or any other scripting language. Many examples even here on SO.

Answer 2

Use concat:

SELECT concat('TRUNCATE TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'

This will of course only generate sql which you need to copy and run yourself.

Answer 3

If you are using linux, you might want to try something like this.

mysql -u [user] -p[password] -e 'use [database]; show tables' | perl -lane 'print "truncate table $F[0]" if /^inventory/i' > [database].sql

Answer 4

Hi all,

Can any body tell me and explain it out how could we trigger two or more Query in a Single SSIS Package, means I want to execute truncate statement for a table and update statement again for the same table (First Truncate and then Update the same table) in single SSIS package.

Thanks & Regards, Rajeev.