Multi Table Delete Syntax for MySQL

Today I needed to do a little clean-up in one of my applications that I re-vamped. There were several tables that had entries that were no longer relevant to the code base so I went about deleting them and any entries that were dependent upon them. It would have been a huge pain to have to go through each table and figure out which entries were associated, so luckily MySQL provides a simple solution. I did have to mess around with this a bit before it would work, mainly due to my prepending the database name in front of the table, ‘db_name.tb_name’, and for some reason that was not well liked… So I instead specified the database I wanted to execute this query on, and removed the database name and it worked like a charm. I needed to remove all references to a module that was no longer within the framework.

The actual query is the following:

DELETE aa, ac, am, aua, aga, ausp, agsp, asp, mi, a
FROM acl_action AS aa
INNER JOIN acl_controller AS ac ON (ac.acl_controller_id = aa.acl_controller_id)
INNER JOIN acl_module AS am ON (am.acl_module_id = ac.acl_module_id)
INNER JOIN acl AS a ON (a.acl_action_id = aa.acl_action_id)
LEFT JOIN acl_user_acl AS aua ON (aua.acl_id = a.acl_id)
LEFT JOIN acl_group_acl AS aga ON (aga.acl_id = a.acl_id)
LEFT JOIN acl_subprivilege As asp ON (asp.acl_id = a.acl_id)
LEFT JOIN acl_group_subprivilege AS agsp ON (agsp.acl_subprivilege_id = asp.acl_subprivilege_id)
LEFT JOIN acl_user_subprivilege AS ausp ON (ausp.acl_subprivilege_id = asp.acl_subprivilege_id)
LEFT JOIN menu_item AS mi ON (mi.acl_id = a.acl_id)
WHERE am.acl_module_id = 1;
Friday, June 10th, 2011 MySQL

Leave a Reply

You must be logged in to post a comment.

[ad code=1 align=center]