Dave Rowe’s Blog

MySQL provides cascading updates / deletes with the relationships, but I tend not to use them, specifically because I want to control just how far these updates and deletes cascade!  But, given a situation where I have a design similar to this:

I would like to be able to remove a single Foo, without having to first remove all the associated data from the other 3 tables.  Or, I know the ID of the Foo I want to remove, so instead of running multiple queries to find the associated rows, lets just knock it out with a single, multi-table delete!

DELETE
  db.zap as z,
  db.baz as bz,
  db.bar as z,
  db.foo as f
FROM
  db.zap as b,
  db.baz as bz,
  db.bar as z,
  db.foo as f
WHERE
  b.baz_id = bz.id AND
  b.zap_id = z.id AND
  bz.foo_id = f.id AND
  z.foo_id = f.id AND
  f.id = ?

This will then remove the rows associated with the single Foo record I’ve referenced, in one fell swoop.