|
Home > Archive > MySQL Server Forum > June 2005 > problem with multi table delete
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
problem with multi table delete
|
|
| Eric Haskins 2005-06-01, 3:23 am |
| 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
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 2005-06-02, 3:23 am |
| 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?
| |
| Jasper Bryant-Greene 2005-06-02, 3:23 am |
| 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.
|
|
|
|
|