Delete Mulitple Rows in Multiple Tables (MySQL)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mrvanjohnson
Forum Contributor
Posts: 137
Joined: Wed May 28, 2003 11:38 am
Location: San Diego, CA

Delete Mulitple Rows in Multiple Tables (MySQL)

Post 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?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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...
User avatar
mrvanjohnson
Forum Contributor
Posts: 137
Joined: Wed May 28, 2003 11:38 am
Location: San Diego, CA

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.)
User avatar
mrvanjohnson
Forum Contributor
Posts: 137
Joined: Wed May 28, 2003 11:38 am
Location: San Diego, CA

Post by mrvanjohnson »

Stated on the same page as the link you previously posted

Of course I see that now :cry: . 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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...
Post Reply