Now for something a little off the beaten track. Let's suppose I'm on a "let's-clean-up-the-database" binge, and I need to check if the various tables are in sync with each other. For example, let's see if, for every customer, there exists at least one branch office in the "branches" table. The quickest way to do this is with a left join and an eyeball check for NULL values.

  mysql> SELECT clients.cid, clients.cname, branches.bid, branches.bdesc 

  mysql> FROM

  clients LEFT JOIN branches USING (cid);

  +-----+-----------------------------+------+--------------------------------+

  | cid | cname | bid | bdesc |

  +-----+-----------------------------+------+--------------------------------+

  | 101 | JV Real Estate | 1011 | Corporate HQ |

  | 101 | JV Real Estate | 1012 | Accounting Department |

  | 101 | JV Real Estate | 1013 | Customer Grievances Department |

  | 102 | ABC Talent Agency | NULL | NULL |

  | 103 | DMW Trading | 1031 | N Region HO |

  | 103 | DMW Trading | 1032 | NE Region HO |

  | 103 | DMW Trading | 1033 | NW Region HO |

  | 104 | Rabbit Foods Inc | 1041 | Branch Office (East) |

  | 104 | Rabbit Foods Inc | 1042 | Branch Office (West) |

  | 110 | Sharp Eyes Detective Agency | 1101 | Head Office |

  +-----+-----------------------------+------+--------------------------------+

  10 rows in set (0.00 sec)

  

Hmmm. It's fairly obvious that we have a record for the customer "ABC Talent Agency", but no corresponding record for one or more branch offices. Since I've decided this is a major offense, I can nuke the client record with a fast DELETE using the customer ID,

  mysql> DELETE FROM clients WHERE cid = 102;

  Query OK, 1 row affected (0.01 sec)

  

or I could combine the two steps above into one using the following subquery:

  mysql> DELETE FROM clients WHERE cid = (SELECT clients.cid FROM clients

  LEFT JOIN branches USING (cid) WHERE bid IS NULL);

  ERROR 1093: You can't specify target table 'clients' for update in FROM clause 
  

Interesting. Do you know why?

It's actually pretty simple (and even makes sense when you think about it). MySQL won't let you delete or update a table's data if you're simultaneously reading that same data with a subquery, as doing so opens up a window where your subquery might reference rows which have already been deleted or altered. Therefore, the table named in an outer DELETE or UPDATE statement cannot appear in the FROM clause of an inner subquery (which is what MySQL more tersely said in its error message above).

Thus, as per the SQL standard, the query above is actually illegal, and should be rewritten using an EXISTS test as follows:

  mysql> DELETE FROM clients WHERE NOT EXISTS (SELECT * FROM branches 

  mysql> WHERE

  branches.cid = clients.cid);

  Query OK, 1 row affected (0.11 sec)

  

A quick glance at the "clients" table will reveal that the offending entry has now been removed.

  mysql> SELECT * FROM clients;

  +-----+-----------------------------+

  | cid | cname |

  +-----+-----------------------------+

  | 101 | JV Real Estate |

  | 103 | DMW Trading |

  | 104 | Rabbit Foods Inc |

  | 110 | Sharp Eyes Detective Agency |

  +-----+-----------------------------+

  4 rows in set (0.22 sec)