Mysql syntax confuse: can't delete multiple table

Question

My mysql command is simple:

DELETE
FROM ts_template_size, ts_thumbnail

I just want delete the two table at the same time. But when I execute it, the client tell me have an sytax error.

If I use chnage the from table to single one, delete in twice, both could success.

However, I check the offical document: http://dev.mysql.com/doc/refman/5.7/en/delete.html the syntax is bellow:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

what is the USING table_references mean? I have to join them together?

You cant do so unless there is a reference between the tables or some joining key. Please provide the table structures and how they are related. So yes you need to join them while doing such delete operation. — Abhik Chakraborty, Oct 12 '16 at 09:23

score 0 · Answer 1 · answered Oct 12 '16 at 09:25

0

If you want to "delete" table use DROP command. If you want to clear all rows in table, use TRUNCATE command.

answered Oct 12 '16 at 09:25

The Multi

21
5

score 0 · Answer 2 · answered Oct 12 '16 at 09:27

you can delete from 2 tables by putting them in transaction. I am not sure if 2 tables can be deleted in a single query..when deleting from any table, you can have 1 or more tables in from clause.

delete table1
from table1, table2 -- that what tbl_references mean above
where table1.c1=table2.c1

what you need: create a driver table with the keys that you can join with both tables, in case deletion from first table would make it difficult to delete from second table..

being transaction
delete table1
from table1 t, drv d
where t.key = d.key

delete table2
from table2 t, drv d
where t.key = d.key
commit -- if successful
rollback -- if unsuccessful..

score 0 · Answer 3 · answered Oct 12 '16 at 09:31

0

You just have to separate it through a semi colon i.e like this way : DELETE FROM ts_template_size; DELETE FROM ts_thumbnail;

answered Oct 12 '16 at 09:31

Aammad Ullah

274
4
11

score 0 · Answer 4 · edited May 23 '17 at 12:33

Possible duplicate of Mysql - delete from multiple tables with one query

You can actually do a DROP table on both the tables (multiple tables) like this:

drop table table1, table2, table3;

Or put the deletes in a transaction:

start transaction
begin
    delete from table1;
    delete from table2;
    delete from table3;
commit;

Or you can create a UDF which contains a dynamic-SQL code, you can provide the list of tables as an array and execute the deletes inside the UDF.

call delete_tables(list_of_tables);

Mysql syntax confuse: can't delete multiple table

4 Answers4