DELETE from multiple tables at once?

[Dan Morgan]

How would this be done? The MYSQL manual isnt very clear and is confusing me more about other things. (Heck I didn't know you could even set the priority of a query! ) I want to delete all rows from 3 different tables where the column id (present in each table) equals something.

Something along the lines of (I know the syntax is wrong)

DELETE * FROM table1, table2, table3 WHERE id='$id'"

Cheers

Dan

[sojomy]

i think that you have to be using MySQL v4 and above, but you're query should work, and probably do what you expect. Delete all rows from all three tables where the idfield is $id. Just make sure each table has an id field

[jgreen]

if you're using phpMyAdmin, throw that sql line in the sql box and see what phpMyAdmin says. If it doesn't say anything make sure $id is indeed being printed to the query string.

[someonewhois]

It should error about not knowing which table ID is for, eh?

I think you have to have multiple where's, one for each table.

Just a thought, because on selects you do

[Dan Morgan]

Thanks for the thoughts guys. It didnt seem to like the * in that statement and threw an " you have an error in your syntax enar...".

Anyhoo, there are only 3 tables with about 10 rows in all to delete each time the action is called so I decided to do it with 3 different DELETE statements.

Cheers

Dan

[sojomy]

It should error about not knowing which table ID is for, eh?
I think you have to have multiple where's, one for each table.
Just a thought, because on selects you do [img]images/smilies/wink.gif[/img]

So what would be the proper query to do what he's looking for? Would this work

DELETE * FROM table1, table2, table3
WHERE table1.id=$id OR table2.id=$id OR table3.id=$id