469,954 Members | 1,722 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,954 developers. It's quick & easy.

problem with multi table delete

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

Jul 23 '05 #1
2 1416
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?

Jul 23 '05 #2
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.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Gordon | last post: by
2 posts views Thread by michael | last post: by
2 posts views Thread by Soldier | last post: by
9 posts views Thread by cheryl | last post: by
7 posts views Thread by Jon Maz | last post: by
2 posts views Thread by =?Utf-8?B?TU1TSkVE?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.