problem with multi table delete

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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,client_rates,avail_apps,req_docs,uplo
ads FROM applications,client_rates,avail_apps,req_docs,uplo
ads where applications.cid='".$var."' or client_rates.cid='".$var."' or
avail_apps.cid='".$var."' or req_docs.cid='".$var."' or

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


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.cid='".$var."'
OR client_rates.cid='".$var."'
OR avail_apps.cid='".$var."'
OR req_docs.cid='".$var."'
OR uploads.cid='".$var."'

work fine?

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.cid = client_rates.cid AND avail_apps.cid
= client_rates.cid AND [...]

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

Site Timeline