2

For example if a customer is deleted, all the related row on other table(S) will be deleted. How to do that mysql, it's a hours i tried many solution but noone worked.enter image description here

cmnardi
  • 1,051
  • 1
  • 13
  • 27
stack
  • 33
  • 1
  • 2
  • 9
  • This is almost impossible to answer without knowing what database you are using and without understanding what the foreign key constraints look like. That said, `Customer_Addresses` has a foreign key with cascade delete then deleting the entry from that table will delete data from `Customer` and `Addresses` – glennstar Sep 02 '16 at 21:24

2 Answers2

3

The easiest way to achieve that is design related tables with ON DELETE CASCADE option.

Basically, when you design a database table that has a foreign key (primary key on other table), you can set ON DELETE CASCADE for that foreign key. That means that when a record is deleted on the primary table (the table where this foreign key is the primary key) all related records on other tables are also deleted.

Therefore, you don't need to think on create "crazy" queries to delete related data on multiple tables.

You can learn more about ON DELETE CASCADE on following link: mysqltutorial.org/mysql-on-delete-cascade

Cristian Gonçalves
  • 892
  • 2
  • 9
  • 18
2

You have to DELETE the Address first, the Customers, and after the relation Or you can try to use DELETE cascate ... but be careful http://www.mysqltutorial.org/mysql-on-delete-cascade/ see this too : MySQL on delete cascade. Test Example

DELETE FROM Addresses WHERE AddressID IN (SELECT AddressID FROM Custormers_Addresses WHERE CustormerID = ID)

THEN .. Delete the customer DELETE FROM Customers WHERE CustomerID = ID

THEN .. delete the relacion DELETE AddressID FROM Custormers_Addresses WHERE CustormerID = ID

Community
  • 1
  • 1
cmnardi
  • 1,051
  • 1
  • 13
  • 27
  • You might try and use a `UNION` as described [here](http://stackoverflow.com/questions/17225080/mysql-delete-from-with-union-subquery-by-in-condition) amongst other places. Alternatively consider using a transactional database like InnoDB, unless you have truly massive amount of data. – glennstar Sep 02 '16 at 21:45