Page 1 of 1
Delete Mulitple Rows in Multiple Tables (MySQL)
Posted: Mon Apr 19, 2004 1:29 pm
by mrvanjohnson
I think I have a fairly easy one for you guys. Can't figure out the syntax to allow me to delete mulitple rows of data out of mulitple tables.
Basically, I have 3 tables. In all the tables there is a field called user_id. When I delete a user, I need to go through all 3 tables and delete any rows that are associated with that user which is determined by user_id.
After reading
DELETE Syntax -Here's what I tried
Code: Select all
DELETE `tb1` . * , `tb2` . * , `tb3` . * WHERE `user_id` = '1'
But I keep getting an error when doing this. Can anyone help me with this?
Posted: Mon Apr 19, 2004 1:45 pm
by JAM
Don't know if this is "the way" but it works for me:
Code: Select all
DELETE table_1, table_2 from table_1, table_2 WHERE table_1.article_id = 11 and table_2.article_id = 11
Addition:
Code: Select all
DELETE
table_1, table_2
from
table_1, table_2
WHERE
table_1.article_id = table_2.article_id and
table_1.article_id = 11
Leaving the first code posted for viewing only. This later is likely better... It should work with inner/left/right joins also after some modification...
Posted: Mon Apr 19, 2004 3:36 pm
by mrvanjohnson
Do I need to set the database type to anything special to get this to work. Currently I am trying this on a MySQL 3.23.54 install and the table type if the default MyISAM.
Posted: Mon Apr 19, 2004 3:41 pm
by JAM
MySQL Manual wrote:From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE.
No. Your MySQL is "outdated" so to speak to use this feature. (Stated on the same page as the link you previously posted.)
Posted: Mon Apr 19, 2004 3:56 pm
by mrvanjohnson
Stated on the same page as the link you previously posted
Of course I see that now

. My mind must have blocked it the two dozen previous times I read that over the past couple of days. Thanks a ton JAM. I've been beating myself up over this and could not figure out why it wasn't working.
Thanks
Posted: Mon Apr 19, 2004 4:00 pm
by JAM
Yah well, it happens to everyone. I liked the old MySQL document pages better, as those allways stated what version the functions/more worked on much clearer.
Today on these newer pages, the version info is abit 'burried' into the pages...