MySQL将子查询中的结果引用更新目标表

错误的SQL语句

为了排序，自然而然地想到了关联子查询。把原表跟自身关联，然后数出来每个地区集合中有多少个价格高于当前记录价格。如果有0个高于当前记录价格，就说明当前记录是该地区集合中价格最高的，ranking里就应该填入1;如果有1个高于当前记录价格，就说明当前记录的价格在该地区集合中排名第2……以此类推。可以看到，使用COUNT数出来高于当前记录价格的价格数后，还要再加上1，才能得到当前记录的ranking。
按照上面的思路很容易编写出以下SQL语句：

UPDATE DistrictProducts AS D1
SET D1.ranking = (SELECT COUNT(D2.price)+1
                  FROM DistrictProducts AS D2
                  WHERE D1.district=D2.district AND D1.price<D2.price);

从逻辑上讲没有问题，在其他的一些数据库中也确实可以完成对数据表的更新，但是在MySQL中执行会报错，报错信息如下：

这是因为MySQL不支持在子查询中引用更新目标表。
我们要更新的就是DistrictProducts这张表，而在子查询的FROM语句中还从这张表查询出数据，这在MySQL中是不被允许的。之所以有这个规定，也是考虑到了数据安全。

解决方法

1. 多嵌套版本

既然不允许在FROM子句里引用更新目标表，那我们就对从目标表里查询出来的数据再查询一次，相当于生成一个临时表。从外层来看，FROM子句里引用的是另外一张表（临时表），这就不会违背刚才提到的原则。
最终写出来的SQL语句如下：

UPDATE DistrictProducts AS D0
SET D0.ranking = (SELECT rank1
                  FROM (SELECT D1.district,D1.name,(SELECT COUNT(D2.price)+1
                                                    FROM DistrictProducts AS D2
                                                    WHERE D1.district=D2.district AND D1.price<D2.price) AS rank1
                                                    FROM DistrictProducts AS D1) AS tt
                  WHERE D0.district=tt.district AND D0.name=tt.name);

上面的语句存在不少嵌套，可以先看里面(SELECT D1.district……) AS tt这部分。这个tt就是我们给临时表取的名字，它有三列，分别是district,name和rank1.其中，rank1就对应着目标表的ranking列。知道了tt的结构，我们就可以在头脑里把这一大串SQL语句简化为：

UPDATE DistrictProducts AS D0
SET D0.ranking = (SELECT rank1
                  FROM tt
                  WHERE D0.district=tt.district AND D0.name=tt.name);

tt里就包含着我们要填写到ranking列的数据，我们只要对目标表和tt也来一个关联子查询，关联条件是district和name都相同，这样就能找到相应的正确ranking值填入数据表。

2. 多表更新减少嵌套版本

如果觉得上面的SQL语句嵌套实在太多了，还可以把生词临时表tt的语句挪到UPDATE子句中：

UPDATE DistrictProducts AS D0,(SELECT D1.district,D1.name,(SELECT COUNT(D2.price)+1
                                                           FROM DistrictProducts AS D2
                                                           WHERE D1.district=D2.district AND D1.price<D2.price) AS rank1
                               FROM DistrictProducts AS D1) AS tt
SET D0.ranking = tt.rank1
WHERE D0.district=tt.district AND D0.name=tt.name;

这回SQL语句的整体结构变成了多表更新。我们会目标表写入数据，另外一个临时表tt是用来提供数据的。

我们还是可以在头脑里把这些语句简化一下：

UPDATE DistrictProducts AS D0,tt
SET D0.ranking = tt.rank1
WHERE D0.district=tt.district AND D0.name=tt.name;

这样一看，结构一目了然。

针对我的业务需求，因为需要内部表关联到需要修改表的字段，所以采用的2. 多表更新减少嵌套版本显得更灵活一些