Major problem with a multiple delete mysql 4.1.12

ehask

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

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,uploads 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."'

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

paulnaj

Hi Eric,

I haven't used InnoDb so I'm not sure what you mean by 'multiple table inserts'.

What is it you are trying to achieve that you can't without InooDb?

As far as I can see from your query ...

DELETE applications
, client_rates
, avail_apps
, req_docs
, uploads
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."'

... it's lacking 'join' statements. Without them ...

FROM applications
, client_rates
, avail_apps
, req_docs,uploads

... is a 'cross-product'. If you don't understand what I mean, then come back and I'll try and explain.

Paul 🙂

ehask

I upgraded to 4.1.12

So I would have INNODB (Transaction support Foreign Keys) and the ability to to mutiple deletes in one query. From what I was reading 3.23 mysql does not support multitable delete

So you would have to do

"Delete from clients where cid = '".$var."' "
"Delete from apps where cid = '".$var."' "
and so on.

The query above works but it deleted all the table data

I need to delete all the client records in each of the table when we delete the customer. I figured I would do with one query instead of multiple since 4.1 supports sub queries.

The main reason for the upgrade was the INNODB they run a gateway and want the use of the
Transaction, Commit, and Rollback features to prevent half of the Txn data from being inserted when one query fails.