How to delete multiple records from multiple table with 1 query?

sorry if i am dumb, but instead of many round trips to MySQL database server like:–

mysql_query("DELETE FROM table_1 WHERE id='$same_id'");
mysql_query("DELETE FROM table_2 WHERE id='$same_id'");
mysql_query("DELETE FROM table_3 WHERE id='$same_id'");
mysql_query("DELETE FROM table_4 WHERE id='$same_id'");
                                   .
                                   .
                                   .
                                   .

how to do it with just 1 mysql_query(); ??

I just started to learn JOIN, GROUP BY…, thus the article at http://www.informit.com/articles/article.aspx?p=30875&seqNum=6 or http://forums.mysql.com/read.php?10,229572,229828#msg-229828 gives me more doubt than insight :confused:

you cannot do it in one query unless the tables can all be joined

If there is a relationship between the tables then you can do

delete from table1, table2, table3, table4
using table1, table2, table3, table4
where table1.id = table2.id and table2.id = table3.id and table3.id=table4.id
and table1.id=‘$same_id’;

is that mean all of the table ‘id’ field must have same number of rows?

sadly i hv different number of id field each table, ‘table1’ 2 rows of id datas, ‘table2’ 1 row, ‘table3’ 100 rows …etc

Sorry not understanding what you are saying. The delete statement does required that each table has an record/entry/row for the id being deleted. If a particular id value does not have an entry in each table the delete statement will not work.

No, it doesn’t matter if the number of rows in one table is different from the number in another. The join syntax joins the tables if there are relative rows/records, based on the column you try to match on.

hth

bazz

If table1 contains ids 1, 2, 3, 4, 5
and table 2 contains ids 1, 2, 3, 4, 5
and table 3 contains ids 1, 2, 3
and table 4 contains ids 3, 4, 5.

Then the delete statement I previously gave

delete from table1, table2, table3, table4
using table1, table2, table3, table4
where table1.id = table2.id and table2.id = table3.id and table3.id=table4.id
and table1.id=‘$same_id’;

will only work for same_id = 3 not for any other id.

What will work is

delete table1, table2, table3, table4
from table1
left join table2 on table2.id = table1.id
left join table3 on table3.id = table1.id
left join table4 on table4.id = table1.id
where table1.id=‘same_id’;

i think i will use this since all of my id’s have SAME value

just tested with real case, only

delete table1, table2, table3, table4
from table1
left join table2 on table2.id = table1.id
left join table3 on table3.id = table1.id
left join table4 on table4.id = table1.id
where table1.id=‘same_id’;

works with my case. trying to understand USING and LEFT JOIN …