Delete Data from All Tables in MySQL

Delete data from all tables in MYSQL

I don't think so (but I've been wrong before). What I tend to do is those cases is a two-step process.

If your DBMS has a command line interface, you can use it to create a script to do the bulk of the work, something like:

db2 "select 'db2 delete from ' | tblname from sysibm.systables
    where owner = 'pax'" >p2.sh
p2.sh

The first bit simply creates a p2.sh file (or a p2.cmd file under Windows) containing a delete from statement for every table owned by pax. Then you just run that command file to do the dirty work. You may want to check it first, of course :-)

Not the one-step process you were looking for but still very simple. I'm assuming here that mysql also has a command line interface.

Update:

The MySQL version of the above looks like it should be:

echo "select 'mysql truncate table ' | table_name
              from information_schema.tables" | mysql >p2.sh
bash p2.sh

This uses the truncate command which is usually more efficient than delete from for deleting all rows. It also uses the proper MySQL system tables to get the table names.

One point though - you may want to put a where clause on that select to limit the tables to those you want deleted. The query as it stands will try to delete every table. One possibility is to limit it with specific table_schema and/or table_type values.

Delete all records in a table of MYSQL in phpMyAdmin

Go to your db -> structure and do empty in required table.
See here:

How to delete specific rows from all tables in a MySQL database?

I think, that is not a query for that, but you can do something like this

SELECT CONCAT('delete from my_db.',table_name,' where set_id > 270') FROM information_schema.tables where table_schema='my_db';

the result of that are all the queries that you need to run. you can copy and run it.

Delete rows from all tables

The easiest way may be the following:

SELECT 
CONCAT('DELETE FROM ',TABLE_NAME," WHERE column1 = 'abc';") comd
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
AND COLUMN_NAME ='column1';

This query will give you output like below:

DELETE FROM TABLE_1 WHERE column1 = 'abc';
DELETE FROM TABLE_2 WHERE column1 = 'abc';
DELETE FROM TABLE_3 WHERE column1 = 'abc';
DELETE FROM TABLE_4 WHERE column1 = 'abc';
.
.
.

Now copy these DELETE commands and execute all.

---

Note:
In other way, you can write a stored program where you can turn these generated command strings into executable command/query through prepare statement.

But you can prefer the easiest way I've suggested above in order to bypass complexity.

Mysql - delete from multiple tables with one query

You can define foreign key constraints on the tables with ON DELETE CASCADE option.

Then deleting the record from parent table removes the records from child tables.

Check this link : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

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

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

How to delete all the tables in a MySQL database?

drop database YOUR_DATABASE;
/* this will delete all the tables for this database */

create database YOUR_DATABASE;
/* added back the database namespace */

---

Related Topics