problem with multi table delete

[Eric Haskins]

Ok I upgraded to 4.1.12 Max so I could start using InnoDb and be able to do
multiple table deletes among others.

I have an app system that I need to delete info from a few tables when we
delete a bogus app or a client we cant place.

Here is the query I wrote that finally worked

DELETE applications,cl ient_rates,avai l_apps,req_docs ,uplo
ads FROM applications,cl ient_rates,avai l_apps,req_docs ,uplo
ads where applications.ci d='".$var."' or client_rates.ci d='".$var."' or
avail_apps.cid= '".$var."' or req_docs.cid='" .$var."' or
uploads.cid='". $var."'

It worked YAY!!! BUT it deleted everything in the tables ARGH!!! good thing
I was on a test DB.

Where did I go wrong? I have tried many variations and this was the only way
it didnt error but it didnt limit to cid = 384. I keep reading the section
on multiple table deletes but it isnt making sense.

SIDENOTE: When I tested the query I hardcoded $var as 384 (id of test
record) and pasted into SQL window on phpmyadmin

thx for any help

Eric

[Jasper Bryant-Greene]

Re: problem with multi table delete

Unless you have relations (foreign keys) set up with your tables, I
would think that it would delete all data from all specified tables
with that query.

Also, why do you have the table names specified twice? Shouldn't:

DELETE FROM applications, client_rates, avail_apps, req_docs, uploads
WHERE applications.ci d='".$var."'
OR client_rates.ci d='".$var."'
OR avail_apps.cid= '".$var."'
OR req_docs.cid='" .$var."'
OR uploads.cid='". $var."'

work fine?

[Jasper Bryant-Greene]

Re: problem with multi table delete

Apologies, I should have read the documentation more carefully.

Your syntax is fine, but you need some join conditions in the WHERE
clause, for example:

WHERE [...] AND applications.ci d = client_rates.ci d AND avail_apps.cid
= client_rates.ci d AND [...]

This is based on my assumption of how your foriegn key relationships
are structured. Modify to suit your situation.