mysql - 使用不同的值更新选择查询中的记录

Question

我有一个 INSERT INTO 查询，可以很好地在添加父记录时创建子记录。我基本上需要运行相同的东西，但不是添加新的子记录，而是需要更新包含总和值的列。

例如，我有以下数据。Shift_Roster_ID 是第一个表的 PK。RosterID 和 ShiftID 都是外键。对于 ShiftID=1，我想为每个唯一的 RosterID 更新 ShiftCount。ShiftCount 是第二个表中 PeriodID 与 ShiftID=1 的 PeriodID 匹配的任何班次的响应值之和。

+-----------------+----------+---------+------------+----------+
| Shift_Roster_ID | RosterID | ShiftID | ShiftCount | Response |
+-----------------+----------+---------+------------+----------+
| 1               | 1        | 1       | 0          | 1        |
+-----------------+----------+---------+------------+----------+
| 2               | 2        | 1       | 0          | 3        |
+-----------------+----------+---------+------------+----------+
| 3               | 3        | 1       | 0          | 8        |
+-----------------+----------+---------+------------+----------+
| 4               | 1        | 2       | 0          | 1        |
+-----------------+----------+---------+------------+----------+
| 5               | 2        | 2       | 0          | 8        |
+-----------------+----------+---------+------------+----------+
| 6               | 3        | 2       | 0          | 7        |
+-----------------+----------+---------+------------+----------+
| 7               | 1        | 2       | 0          | 7        |
+-----------------+----------+---------+------------+----------+
| 8               | 2        | 3       | 0          | 7        |
+-----------------+----------+---------+------------+----------+
| 9               | 3        | 3       | 0          | 6        |
+-----------------+----------+---------+------------+----------+

+---------+----------+
| ShiftID | PeriodID |
+---------+----------+
| 1       | 1        |
+---------+----------+
| 2       | 1        |
+---------+----------+
| 3       | 2        |
+---------+----------+

结果应该是

+-----------------+----------+---------+------------+----------+
| Shift_Roster_ID | RosterID | ShiftID | ShiftCount | Response |
+-----------------+----------+---------+------------+----------+
| 1               | 1        | 1       | **2**      | 1        |
+-----------------+----------+---------+------------+----------+
| 2               | 2        | 1       | **11**     | 3        |
+-----------------+----------+---------+------------+----------+
| 3               | 3        | 1       | **15**     | 8        |
+-----------------+----------+---------+------------+----------+
| 4               | 1        | 2       | 0          | 1        |
+-----------------+----------+---------+------------+----------+
| 5               | 2        | 2       | 0          | 8        |
+-----------------+----------+---------+------------+----------+
| 6               | 3        | 2       | 0          | 7        |
+-----------------+----------+---------+------------+----------+
| 7               | 1        | 2       | 0          | 7        |
+-----------------+----------+---------+------------+----------+
| 8               | 2        | 3       | 0          | 7        |
+-----------------+----------+---------+------------+----------+
| 9               | 3        | 3       | 0          | 6        |
+-----------------+----------+---------+------------+----------+

标签： mysqlsql-update