解决MySQL报错:You can‘t specify target table ‘region‘ for update in FROM clause

MySQL中有一个很常见的错误，就是当在UPDATE或DELETE语句中使用包含子查询的FROM子句时，会报错，错误的具体内容是：“You can't specify target table 'tableName' for update in FROM clause”。

该错误的原因是MySQL对于一个查询语句只能生成一个结果集，如果查询子句中使用了目标表，则在更新时会产生不必要的歧义，使MySQL无法确认目标表和子查询中使用的表是否是同一个表，所以MySQL会报错。

解决上述错误，我们需要用到MySQL5.6版本引入的派生表（derived table）的概念。在解决这个问题的时候，我们需要将子查询的结果放到一个临时表中。派生表实际上是一个SELECT语句的结果集，我们可以在SELECT语句中将子查询作为一个派生表来使用。

以下我们来看一下具体的示例如下：

示例1：

假设我们有两个表：region和counties，其中region表中包含id和name字段，而counties表中包含id和region_id两个字段。现在我们要将region表中某个名称为‘abc’的地区下所有的县名都修改为‘def’。

我们想要得到所有属于名称为‘abc’的地区下的县的id，并将它们在counties表中对应的县名修改为‘def’，但是有如下SQL语句的话：

UPDATE counties SET name = ‘def’ WHERE region_id IN (SELECT id FROM region WHERE name = ‘abc’);

则会产生报错：“You can’t specify target table ‘region‘ for update in FROM clause”。这是因为这个语句涉及到了两个表的查询，counties和region，而子查询中的region作为子查询和目标表counties存在歧义。这时我们就可以用派生表来解决这个错误。

我们可以使用如下SQL语句：

UPDATE counties SET name = ‘def’ WHERE region_id IN (SELECT * FROM (SELECT id FROM region WHERE name = ‘abc’) AS tempTable);

这里，我们将子查询放到了一个临时表tempTable中，并用该派生表tempTable替换了原先子查询。

示例2：

现在考虑一个稍微复杂一点的例子，我们想要先在一个表中找到一些记录的id，然后用在这些记录的基础上更新表中的某些数据。假设我们有一张叫做users的表，其中包含id、name和score三个字段。现在我们想要将score小于50的用户的name前缀修改为TEST。

如果我们按照如下的SQL语句来写，则会产生报错：“You can’t specify target table ‘users‘ for update in FROM clause”：

UPDATE users SET name = CONCAT(‘TEST_’,name) WHERE id IN (SELECT id FROM users WHERE score < 50);

实际上，我们可以用派生表来解决这个问题，具体的SQL语句如下：

UPDATE users SET name = CONCAT(‘TEST_’,name) WHERE id IN (SELECT * FROM (SELECT id FROM users WHERE score < 50) as derivedTable);

这里，我们先在SELECT语句中使用了WHERE条件来找到score小于50的用户的id，然后将其放到一个派生表中（即derivedTable），最后我们在UPDATE语句中根据该派生表的结果来进行更新。这样我们就成功地解决了该错误。