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)