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