Truncate all tables in MySQL database that match a name pattern

Question

I need to clear all my inventory tables.

I've tried this:

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

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.