kessels1234 Posted March 1, 2010 Share Posted March 1, 2010 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 Quote Link to comment Share on other sites More sharing options...
jl5501 Posted March 1, 2010 Share Posted March 1, 2010 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 Quote Link to comment Share on other sites More sharing options...
Mchl Posted March 1, 2010 Share Posted March 1, 2010 Actually you can DELETE klant , repair FROM klant LEFT JOIN repair ON klant.klant_id=repair.klant_id WHERE klant_id = ".$validid." Quote Link to comment Share on other sites More sharing options...
jl5501 Posted March 1, 2010 Share Posted March 1, 2010 Thank you Mchl, I just saw that. I am not so sure it is a good idea though, but thanks anyway Quote Link to comment Share on other sites More sharing options...
kessels1234 Posted March 1, 2010 Author Share Posted March 1, 2010 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 Quote Link to comment Share on other sites More sharing options...
jl5501 Posted March 1, 2010 Share Posted March 1, 2010 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. Quote Link to comment Share on other sites More sharing options...
Mchl Posted March 1, 2010 Share Posted March 1, 2010 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 Quote Link to comment Share on other sites More sharing options...
kessels1234 Posted March 1, 2010 Author Share Posted March 1, 2010 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.