Delete from multiple tables

[kessels1234]

Hi,

 

I want to perform a delete on multiple records from different tables.

 

I have a table called KLANTEN(=customers) and I have a table called REPAIR:

 

A customer can have more than 1 repairs. So when I delete a customer, also all repairs should be deleted to.

 

My tables:

1. KLANTEN

klant_id

title

firstname

lastname

etc.......

 

2. REPAIR

repair_id

klant_id

instrument

etc.....

 

I though that following sql statement would do the job but obvious it doesn't

case "klant":
		$repairsql = "DELETE FROM klant LEFT JOIN repair ON klant.klant_id=repair.klant_id WHERE klant_id = ".$validid.";";
		mysql_query($repairsql);
		if (!$repairsql){
		echo "Gegevens niet verwijderd";	
		}
		else{
		header("Location: ".$livesite. "/admin/klant_list.php");
		}
		break;

I get the $validid through the url via a $_GET

 

What am I doing wrong?

 

Thanks for your help in advance.

Danny

[jl5501]

You will need to do 2 deletes

 

the first to delete the repairs where klant_id = $validid

 

then delete the entry from the klant table

 

you cannot delete through a join

[Mchl]

Actually you can

DELETE klant , repair FROM klant LEFT JOIN repair ON klant.klant_id=repair.klant_id WHERE klant_id = ".$validid."

[jl5501]

Thank you Mchl, I just saw that. I am not so sure it is a good idea though, but thanks anyway

[kessels1234]

Hi,

 

thanks for your help. I got it working and the final sql statement =

$repairsql = "DELETE klanten, repair  FROM klanten LEFT JOIN repair ON klanten.klant_id=repair.klant_id WHERE klanten.klant_id = ".$validid.";";

 

jl5501:Why wouldn't it be a good idea to do it this way?

 

Thanks again everyone

[jl5501]

It is just my opinion. Thinking about it, there is not really a problem, and it does have the advantage of being slightly more atomic, in that there cannot be a failure between the delete of the repairs entries and the main table.

 

Historically, I do not believe you were able to do this, which just goes to show that you are never to old to learn.

[Mchl]

Yeah, I also prefer to delete from one table at a time... feels safer somehow...

 

As for history...

http://dev.mysql.com/doc/refman/4.1/en/delete.html

 

It's been around at least since 4.1

[kessels1234]

Thanks for the reply.

 

I guess it is just a matter of opinion than. About safer feelings: The main goal is to delete all records related to the customer and that is what this does. Multiple ways lead to Rome........

 

SOLVED