sql - 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.