mysql - 多重加入并在mysql中设置

Question

我有 13 个具有相同结构的表:

id  switch_name area

每个表包含特定区域的不同 switch_name。例如，

table1:
id    switch_name area
56    xxxx        A
77    yyyy        A
table2:
id    switch_name area
89    zzzz        B
78    uuuu        B

我需要使用这些表来更新另一个包含 id 的表“results”，并等待用区域值填充。

所以我会执行这个命令:

UPDATE results JOIN table1 ON results.switch_id = table1.id SET results.area = table1.area

这对于一张表来说效果很好，但是我想加入所有这 13 个表。我们以其中两个为例，我可以这样写查询吗:

 UPDATE results JOIN table1 ON results.switch_id = table1.id JOIN table2 ON results.switch_id = table2.id SET results.area = table1.area, results.area = table2.area

或

UPDATE results JOIN table1 ON results.switch_id = table1.id SET results.area = table1.area JOIN table2 ON results.switch_id = table2.id SET results.area = table2.area

非常感谢

Answer 1

如果所有 tableN 表都具有相同的结构但内容不同，UNION ALL 将它们全部放在一个子查询中并与之连接。

UPDATE 
  results
  JOIN (
    SELECT id, switch_name, area FROM table1
    UNION ALL 
    SELECT id, switch_name, area FROM table2
    UNION ALL
    ..
    ..
    SELECT id, switch_name, area FROM table13
  ) alltogether ON results.switch_id = alltogether.id
SET results.area = alltogether.area
WHERE <conditions for update>

从长远来看，如果这些表都相似但包含同一数据集的不同子集，则可能应该将它们组合成一个表，该表有一个列来区分它们的来源。

使用类似下面的方法将它们合并到一个表中:

INSERT INTO new_combined_table 
  (id, switch_name, area, source)
  SELECT id, switch_name, area, 't1' AS source FROM table1
  UNION ALL
  SELECT id, switch_name, area, 't2' AS source FROM table2
  UNION ALL
  SELECT id, switch_name, area, 't3' AS source FROM table3
  ..
  ..
  UNION ALL 
  SELECT id, switch_name, area, 't13' AS source FROM table13