Luiscencio Luiscencio - 3 months ago 24
MySQL Question

Truncate all tables in MySQL database that match a name pattern

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

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.

Comments