mysql - 将表 1 的单位列更新为表 2

Question

表格:

mysql> select * from table1;
+------+------+------+
| no   | nm   | unit |
+------+------+------+
|    1 | ABC  |   10 |
|    2 | ACX  |   20 |
|    3 | AYU  |   30 |
+------+------+------+
3 rows in set (0.01 sec)

mysql> select * from table2;
+------+------+------+
| no   | nm   | unit |
+------+------+------+
|    1 | ABC  |   40 |
|    2 | ACX  |   20 |
+------+------+------+
2 rows in set (0.00 sec)

要求的输出:

mysql> select * from table2;
+------+------+------+
| no   | nm   | unit |
+------+------+------+
|    1 | ABC  |   50 |
|    2 | ACX  |   40 |
|    3 | AYU  |   30 |
+------+------+------+
3 rows in set (0.00 sec)

Answer 1

如果您想更新 Table2 作为所需的输出，那么您需要先更新已经存在的行，然后从 Table1 插入新行:

您可以使用 JOIN 更新单元:

UPDATE Table2 t2
  JOIN 
       (SELECT nm, SUM(unit) unit
       FROM
       (
           SELECT * FROM Table1 t1
           UNION ALL
           SELECT * FROM Table2 t2
       ) tbl
       GROUP BY nm
       ) tbl1
    ON t2.nm = tbl1.nm
   SET t2.unit = tbl1.unit;

然后您可以添加表 1 中不存在于表 2 中的行(例如 nm=AYU)

INSERT INTO Table2
SELECT t1.`no`, t1.`nm`, t1.`unit`
  FROM Table1 t1
  LEFT JOIN Table2 t2
    ON t1.nm = t2.nm
 WHERE t2.nm IS NULL;

输出:

SELECT * FROM Table2;
| NO |  NM | UNIT |
|----|-----|------|
|  1 | ABC |   50 |
|  2 | ACX |   40 |
|  3 | AYU |   30 |

参见this SQLFiddle

记住先更新表格。否则它会复制单位。