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
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
Give it a Try !!!
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.
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?
I have re-used the RonaldoMYSQL db answer to drop a table altogether, posting it here so anyone can use it for his/her convenience ,
SELECT delete_list /*CONCAT('DELETE ',delete_list,' FROM ',table_list)*/
INTO @mass_delete_table_command
FROM
(
SELECT GROUP_CONCAT('Drop table ',table_name,'; ') delete_list,
GROUP_CONCAT(table_schema,'.',table_name) table_list
FROM
(
SELECT table_schema,table_name
FROM information_schema.tables
WHERE table_name LIKE '%$%'
AND table_schema = 'you schema name'
) ListOfTables
) DeleteParameters;
SELECT @mass_delete_table_command;
A comma is fetched in my query so I modified that statement with a replace all and then executed it manually.
