[SOLVED] delete rows from a table when a specific row from another table has been deleted

delete rows from a table when a specific row from another table has been deleted

I have Group and each group has contacts associated with it. When a user deletes a group, if the group is not empty then it will alert them that all contacts in that group will be deleted if they continue. Well anyways, so my problem is setting up that feature.

I have tried to figure out how I can delete all contacts that belong to that group and delete the group as well.

Before I continue I'm wondering but is there a sorta of automated way of doing this via foreign keys?

Well if not its ok, this is my query but SQL Workbench is throwing out the following error

DELETE c
FROM `list_`.`contacts` AS c
INNER JOIN `list_`.`groups` AS g ON c.group_id = g.id
WHERE g.group = 'School' 
  AND c.user_id = 2;

error:

Error Code: 1046 No database selected

really confused here, also I have also tried c.*

Solution

MySQL supports multi-table deletions in a single statement - use:

DELETE c, g
  FROM `list_`.`contacts` AS c
  JOIN `list_`.`groups` AS g ON c.group_id = g.id
                            AND g.group = 'School' 
 WHERE c.user_id = 2;

Regarding error code 1046, when using WorkBench make sure the appropriate database/catalog is selected in the drop down menu found above the Object Browser tab. You can specify the default schema/database/catalog for the connection - click the "Manage Connections" options under the SQL Development heading of the Workbench splash screen.