对于这个错误信息：
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)
阅读(7947) | 评论(1) | 转发(0) |
