Delete data from all tables but keep the data of one table in MySQL

Question

I need to delete the data from all tables but one table in my database. Let's assumme the database is called my_database and the table in which data should be preserved is called my_important_table, so is there any way to achieve this?

Yes of course. Are you doing this from the mysql console or a program you want to write — RiggsFolly, Apr 30 '19 at 14:00
Then you will have to repeat a `truncate ` on each table you want to empty — RiggsFolly, Apr 30 '19 at 14:02
@RiggsFolly, I have limited experience with MySQL, so can you provide a link or an answer, and is there any way to create a sort of for loop which a condition that does not delete data if the table is called `my_important_table`? (if you provide any reference I can even provide an answer myself) — lmiguelvargasf, Apr 30 '19 at 14:05
https://stackoverflow.com/questions/1912813/truncate-all-tables-in-a-mysql-database-in-one-command — RiggsFolly, Apr 30 '19 at 14:08

lmiguelvargasf · Accepted Answer · 2019-04-30T15:43:15.343

I was able to figure out this problem thanks to these questions:

Truncate all tables in a MySQL database in one command? (check most voted answer)
mysql: What is the right syntax for NOT LIKE? (check validated answer)

The following command worked properly for me:

mysql -u root -p -Nse "SHOW TABLES WHERE \`Tables_in_my_database\` != 'my_important_table'" my_database | while read table; do echo "SET FOREIGN_KEY_CHECKS = 0; truncate table $table;"; done | mysql -u root -p my_database

The following command is the same as the previous one, but I split it into multiple lines to improve visualization.

mysql -u root -p -Nse "SHOW TABLES WHERE \`Tables_in_my_database\` != 'my_important_table'" my_database | \
while read table; do echo "SET FOREIGN_KEY_CHECKS = 0; truncate table $table;"; done | \
mysql -u root -p my_database

Delete data from all tables but keep the data of one table in MySQL

1 Answers1