mysql使用另一个表中的数据更新表

Question

我的数据库中有一个像这样的表结构:

/*city*/
+----------+------------+
|    id    |   name     |
|-----------------------|
|    1     | Gotham     |
|    2     | Metropolis |
|    3     | Smallville |
|    4     | Fawcett    |
+----------+------------+   

/*district*/ 
+----------+------------+------------+
|    id    |   name     |   city_id  |
|------------------------------------|
|    1     |     A      |      1     |
|    2     |     B      |      1     |
|    3     |     C      |      2     |
|    4     |     D      |      2     |
|    5     |     E      |      2     |
|    6     |     F      |      3     |
|    7     |     G      |      3     |
|    8     |     H      |      4     |
+----------+------------+------------+

/*distance*/
+----------+-------------+------------------+-------------------------+---------+
|    id    | origin_city | city_destination |  district_destination   |  length |
|---------------------------------------------------------------------|---------|
|    1     |     2       |         2        |            1            |    4    |
|    2     |     3       |         3        |            1            |    5    |
|    3     |     1       |         1        |            2            |    6    |
|    4     |     2       |         2        |            3            |    5    |
|    5     |     4       |         4        |            1            |    8    |
|    6     |     4       |         2        |            4            |    9    |
|    7     |     4       |         3        |            5            |    11   |
|    8     |     1       |         4        |            6            |    13   |
+----------+-------------+------------------+-------------------------+---------+

表区通过city_id外键连接到城市表，距离表同时连接到城市和区表，问题是如果距离表中存在错误 city_destination 数据与 district_destination 不匹配，我需要解决这个问题，但我不知道如何使用更新查询来解决这种麻烦，以显示错误我使用此查询的 city_destination 数据:

SELECT a.* FROM distance a, district b WHERE a.district_destination = b.id AND a.city_destination != b.city_id

Answer 1

首先，放弃连接操作的老式逗号语法。使用 JOIN 关键字并将连接谓词移至 ON 子句。编写一个 SELECT 查询，返回要更新的现有行(以及 PK 和要分配的新值。(这看起来是您所得到的。)

假设我们要替换 distance 表的 city_destination 列中的值，并发现该列在功能上依赖于 district_destination >...

从返回要更新的行的查询开始。

SELECT ce.id                  AS id
     , ce.district_destination AS district_destination 
     , ce.city_destination    AS old_city_destination
     , ct.city_id             AS new_city_destination        
  FROM distance ce
  JOIN district ct
    ON ct.id = ce.district_destination
   AND NOT ( ct.city_id <=> ce.city_destination )
 ORDER BY ce.id

在 MySQL 中，多表更新非常简单。 MySQL 引用手册中记录了该语法。

首先，我们将其编写为 SELECT，使用之前的查询作为内联 View

SELECT t.id
     , s.new_city_destination
  FROM ( SELECT ce.id                  AS id
              , ce.district_destination AS district_destination 
              , ce.city_destination    AS old_city_destination
              , ct.city_id             AS new_city_destination        
           FROM distance ce
           JOIN district ct
             ON ct.id = ce.district_destination
            AND NOT ( ct.city_id <=> ce.city_destination )
          ORDER BY ce.id
       ) s
  JOIN distance t
    ON t.id = s.id

然后我们可以将其转换为 UPDATE 语句。将 SELECT ... FROM 替换为 UPDATE 并在末尾添加 SET 子句。 (如果有的话，位于 WHERE 子句之前。)

UPDATE ( SELECT ce.id                  AS id
              , ce.district_destination AS district_destination 
              , ce.city_destination    AS old_city_destination
              , ct.city_id             AS new_city_destination        
           FROM distance ce
           JOIN district ct
             ON ct.id = ce.district_destination
            AND NOT ( ct.city_id <=> ce.city_destination )
          ORDER BY ce.id
       ) s
  JOIN distance t
    ON t.id = s.id
   SET t.city_destination = s.new_city_destination