Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to clear all tables with one request? I have tried the following with no result.

DELETE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o
FROM jos_bet_details as a, jos_bet_1x2 as b, jos_bet_1x2_best as c, jos_bet_1x2_prev as d,
     jos_bet_1x3 as e, jos_bet_1x3_best as f, jos_bet_1x3_prev g, jos_bet_hcp as h,
     jos_bet_hcp_best i, jos_bet_hcp_prev j, jos_bet_ou k,jos_bet_ou_best l,
     jos_bet_ou_prev m, jos_bet_debug n, jos_bet_deleted o
share|improve this question
1  
I updated the query in my answer so that you only have to gather the table names once instead of twice. –  RolandoMySQLDBA Aug 24 '11 at 15:07
    
@iUngi - do you have FKs between those tables? What's the exact error message? –  Marian Aug 26 '11 at 11:59
add comment

3 Answers

up vote 3 down vote accepted

Here is your solution

SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN
        ('jos_bet_details','jos_bet_1x2','jos_bet_1x2_best','jos_bet_1x2_prev',
         'jos_bet_1x3','jos_bet_1x3_best','jos_bet_1x3_prev','jos_bet_hcp',
         'jos_bet_hcp_best','jos_bet_hcp_prev','jos_bet_ou','jos_bet_ou_best',
         'jos_bet_ou_prev','jos_bet_debug','jos_bet_deleted')
    ) ListOfTables
) DeleteParameters;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;

Here is the principle behind this: You want to use information_schema.tables to build the DELETE query dynamically for you.

First collect the names of the tables in a variable called 'mass_delete_table_command' as a delete list and a table list. Then, execute it as a prepared statement.

Here is sample code from creating 4 tables, adding records them, and zapping the records:

use test
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
CREATE TABLE t1 (n int NOT NULL);
CREATE TABLE t2 like t1;
CREATE TABLE t2 like t1;
CREATE TABLE t3 like t1;
CREATE TABLE t4 like t1;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
INSERT INTO t4 SELECT * FROM t1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN ('t1','t2','t3','t4')
    ) ListOfTables
) DeleteParameters;
SELECT @mass_delete_table_command;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;

I ran this in MySQL 5.5.12 on my PC. Here is the output:

mysql> use test
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
Database changed
mysql> DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (n int NOT NULL);
CREATE TABLE t2 like t1;
CREATE TABLE t2 like t1;
CREATE TABLE t3 like t1;
CREATE TABLE t4 like t1;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
INSERT INTO t4 SELECT * FROM t1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN ('t1','t2','t3','t4')
    ) ListOfTables
) DeleteParameters;
SELECT @mass_delete_table_command;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
Query OK, 0 rows affected (0.04 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS t3;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS t4;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t1 (n int NOT NULL);
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t2 like t1;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE t2 like t1;
ERROR 1050 (42S01): Table 't2' already exists
mysql> CREATE TABLE t3 like t1;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t4 like t1;
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
Query OK, 7 rows affected (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 SELECT * FROM t1;
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t3 SELECT * FROM t1;
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t4 SELECT * FROM t1;
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT A.*,B.*,C.*,D.* FROM
    -> (SELECT COUNT(1) t1count FROM t1) A,
    -> (SELECT COUNT(1) t2count FROM t2) B,
    -> (SELECT COUNT(1) t3count FROM t3) C,
    -> (SELECT COUNT(1) t4count FROM t4) D;
+---------+---------+---------+---------+
| t1count | t2count | t3count | t4count |
+---------+---------+---------+---------+
|       7 |       7 |       7 |       7 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
    -> INTO @mass_delete_table_command
    -> FROM
    -> (
    ->     SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    ->     GROUP_CONCAT(table_schema,'.',table_name) table_list
    ->     FROM
    ->     (
    ->         SELECT table_schema,table_name
    ->         FROM information_schema.tables
    ->         WHERE table_schema=database() AND
    ->         table_name IN ('t1','t2','t3','t4')
    ->     ) ListOfTables
    -> ) DeleteParameters;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @mass_delete_table_command;
+-----------------------------------------------------------------+
| @mass_delete_table_command                                      |
+-----------------------------------------------------------------+
| DELETE t1.*,t2.*,t3.*,t4.* FROM test.t1,test.t2,test.t3,test.t4 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s1 FROM @mass_delete_table_command;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s1;
Query OK, 28 rows affected (0.08 sec)

mysql> DEALLOCATE PREPARE s1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.*,B.*,C.*,D.* FROM
    -> (SELECT COUNT(1) t1count FROM t1) A,
    -> (SELECT COUNT(1) t2count FROM t2) B,
    -> (SELECT COUNT(1) t3count FROM t3) C,
    -> (SELECT COUNT(1) t4count FROM t4) D;
+---------+---------+---------+---------+
| t1count | t2count | t3count | t4count |
+---------+---------+---------+---------+
|       0 |       0 |       0 |       0 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql>

This will definitely work for you as long as

  • you are standing in the correct database at the time
  • you suppply the list of tables you want dropped to the subqueries

Give it a Try !!!

share|improve this answer
add comment

You'd have to build a dynamic sql statement that's going to delete data from all tables, one by one. You will find a proper example on truncating data in all tables in a database in this question. There the issue is about truncating all tables in a MS SQL 2000 database using Transact-Sql. Also there we talk about truncating tables (non logged operation, has some limitations), not just deleting, so if you need delete, you'll have to change the operation from truncate to delete.

The problem here is related to the order of precedence of the statements when looking at parent/children relationship between the tables, as you can't empty a parent table before its' children, so you'll have to check the order of the delete statements.

I'd do something like:

declare @sql nvarchar(max), @enter char(2);
set @enter = char(13)+char(10);
set @sql = '';

select @sql = @sql + 'delete from ' + table_schema + '.' + table_name + ';' + @enter
from information_schema.tables

print @sql;
--exec @sql; --only after review!!!

This is a T-SQL example of dynamic sql using system view information_schema.tables that exists also in My-SQL. You'll have to translate the variable declaration and other small language issues.

EDIT 1 - reason for delete: I wasn't aware of MySQL's multiple table DELETE statement. My bad!

Edit 2 - reason for undelete: I saw that the accepted answer was using this technique, so it seems this answer is valid. What happened with the multiple table delete statement though?

share|improve this answer
1  
Technically truncate is not a non-logged operation, it is a minimally logged operation. –  mrdenny Aug 23 '11 at 22:39
    
You're right, minimally logged. –  Marian Aug 26 '11 at 9:00
    
    
@Jack - I'm sure you're right.. but in the question there was no info regarding FKs :-). Also there was no info regarding the error that was obtained when running the statement... So it was a blind try :-). –  Marian Aug 26 '11 at 11:49
    
@Marian - I couldn't quite believe it so I tried it myself and updated my answer. Not very ACID :-) –  Jack Douglas Aug 26 '11 at 11:51
add comment

Your delete syntax looks good to me:

create table t1(v1 serial);
create table t2(v2 serial);
insert into t1 values(default);
insert into t2 values(default);

select count(*) from (select * from t1 union all select * from t2) z;
+----------+
| count(*) |
+----------+
|        2 |
+----------+

delete a, b from t1 as a, t2 as b;

select count(*) from (select * from t1 union all select * from t2) z;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Note that foreign key constraints may cause this to fail. If you have circular foreign keys (ie from table A to table B and from table B to table A) then you may need to make deletes cascade or drop (or disable) the constraints before deleting.

share|improve this answer
2  
+1 for the MySQL syntax. Not very portable though :) hehe –  Andomar Aug 24 '11 at 8:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.