MYSQL 不允许在子查询的同时删除原表数据的解决方法

对于这个错误信息：
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)