对于这个错误信息：

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

　　或者：

　　ERROR 1093 (HY000): You can't specify target table 'clients' for delete in FROM clause

　　一直以来我以为只有一种办法。不过今天翻开以前的书，发现还有一个方法。

　　表结构和示例数据：

　　mysql> show create table branches\G

　　*************************** 1. row ***************************

　　Table: branches

　　Create Table: CREATE TABLE `branches` (

　　`bid` int(11) NOT NULL,

　　`cid` int(11) NOT NULL,

　　`bdesc` varchar(1000) NOT NULL,

　　`bloc` char(2) NOT NULL,

　　PRIMARY KEY (`bid`),

　　KEY `cid` (`cid`),

　　CONSTRAINT `branches_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `clients` (`cid`)

　　) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　1 row in set (0.00 sec)

　　mysql> select * from branches;

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

　　| bid | cid | bdesc | bloc |

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

　　| 1011 | 101 | Corporate HQ | CA |

　　| 1012 | 101 | Accounting Department | NY |

　　| 1013 | 101 | Customer Grievances Department | KA |

　　| 1031 | 103 | N Region HO | ME |

　　| 1032 | 103 | NE Region HO | CT |

　　| 1033 | 103 | NW Region HO | NY |

　　| 1041 | 104 | Branch Office (East) | MA |

　　| 1042 | 104 | Branch Office (West) | CA |

　　| 1101 | 110 | Head Office | CA |

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

　　9 rows in set (0.00 sec)

　　mysql> show create table clients\G

　　*************************** 1. row ***************************

　　Table: clients

　　Create Table: CREATE TABLE `clients` (

　　`cid` int(11) NOT NULL,

　　`cname` varchar(64) NOT NULL,

　　PRIMARY KEY (`cid`)

　　) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　1 row in set (0.00 sec)

　　mysql> select * from clients;

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

　　| cid | cname |

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

　　| 101 | JV Real Estate |

　　| 102 | ABC Talent Agency |

　　| 103 | DMW Trading |

　　| 104 | Rabbit Foods Inc |

　　| 110 | Sharp Eyes Detective Agency |

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

　　5 rows in set (0.00 sec)

　　mysql> delete from clients where cid = (select clients.cid from clients left join branches using(cid) where bid is null);

　　ERROR 1093 (HY000): You can't specify target table 'clients' for update in FROM

　　clause

　　解决办法

　　1、利用变量赋值。

　　mysql> select @m_cid:=clients.cid from clients left join branches using(cid) where bid is null;

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

　　| @m_cid:=clients.cid |

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

　　| 102 |

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

　　1 row in set (0.00 sec)

　　mysql> delete from clients where cid = 102;

　　Query OK, 1 row affected (0.05 sec)

　　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.00 sec)

　　2、用EXISTS关键字和相关子查询：(不过这个没有之前的效率高)

　　mysql> insert into clients values(102,'ABC Talent Agency');

　　Query OK, 1 row affected (0.05 sec)

　　mysql> delete from clients where not exists

　　-> (

　　-> select * from branches where branches.cid = clients.cid

　　-> );

　　Query OK, 1 row affected (0.06 sec)

　　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.00 sec)