Welcome to WebmasterWorld Guest from 54.152.38.154

Forum Moderators: coopster & jatar k

Deleting over multiple tables

     
4:38 pm on Apr 23, 2019 (gmt 0)

Junior Member

5+ Year Member

joined:Oct 19, 2011
posts: 198
votes: 1


I'm looking to be able to delete data that spans over several tables, but I'm not sure how to do it. I could use a ton of DELETE statements, but is there a way to streamline the process? Thanks!
7:03 pm on Apr 23, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3473
votes: 76


will you always be doing the same series of DELETEs? i'm assuming that the different tables are all connected by primary keys?

if so you could set up a TRIGGER on the database, set it to fire when you make a delete from 'table_A' ... the trigger should be programmed to delete from table_b, table_c or whatever you want.
that way you will only have to do the delete from table_a as the other deletes will happen automatically.
the downside of this is if you ever want to only delete from table_a then you won't be able to without the trigger firing.

alternatively create a stored function (stored procedure) on the database with all the statements in it (wrapped up in a transaction to ensure all deletes are done), and then just call the stored procedure from your php

alternatively you should be able to build a single MySQL statement to delete from multiple tables at once ... [dev.mysql.com...]
3:06 pm on Apr 30, 2019 (gmt 0)

Junior Member

5+ Year Member

joined:Oct 19, 2011
posts: 198
votes: 1


I've tried the TRIGGER, but it's not working.
$sql = "CREATE TRIGGER `delete_vehicle` AFTER DELETE ON `vehicle`
FOR EACH ROW BEGIN
DELETE FROM 'oilchange' WHERE 'VehicleID'='".$id."';
DELETE FROM 'battery' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'brakes' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'custom' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'eos' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'muffler' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'tires' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'wireplugs' WHERE 'VehicleID' = '".$id."';
END;
";
Where am I going wrong?
3:55 pm on Apr 30, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2634
votes: 5


Assuming you want to delete a signle record accress many tables.

The best option is to add foreign key indexes and to define them to cascading delete.

That way you only have to delete the "parent" record and all related records will delete with it.

For example a user who can make a post.

User
----------
id
name
email


Post
----------
id
user_id
message

You would add a foreign key to Post table, and set to cascade on delete

ALTER TABLE Post
FOREIGN KEY (user_id) REFERENCES User (id) ON DELETE CASCADE

Then if you delete a row from the User table, any related Posts will be deleted.

Hope this helps
6:03 pm on Apr 30, 2019 (gmt 0)

Junior Member

5+ Year Member

joined:Oct 19, 2011
posts: 198
votes: 1


Actually what I want to do is delete all entries with the id across all tables. Is a TRIGGER the right way to do that?
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members