我目前正在将数据从一个结构转换为另一个结构，在这个过程中，我必须从组中的第一个条目中获取一个状态id，并将其应用于同一组中的最后一个条目。当使用硬编码值时，我可以很好地定位和更新组中的最后一个项，但是当我尝试使用第一个条目的状态标识时，我遇到了麻烦。下面是数据结构的一个示例。

-----------------------------------------------------------
| id | ticket_id | status_id | new_status_id | created_at |
-----------------------------------------------------------
| 1  | 10        | NULL      | 3             | 2018-06-20 |
| 2  | 10        | 1         | 1             | 2018-06-22 |
| 3  | 10        | 1         | 1             | 2018-06-23 |
| 4  | 10        | 1         | 1             | 2018-06-26 |
-----------------------------------------------------------

所以我们的想法是 new_status_id 并将其应用于id 4的同一字段。
下面是使用硬编码值时的查询

UPDATE Communications_History as ch
    JOIN
    ( 
        SELECT communication_id, MAX(created_at) max_time, new_status_id
        FROM Communications_History
        GROUP BY communication_id
    ) ch2 
    ON ch.communication_id = ch2.communication_id AND ch.created_at = ch2.max_time
SET ch.new_status_id = 3

但是当我使用下面的查询时，我得到 Unknown column ch.communication_id in where clause ```
UPDATE Communications_History as ch
JOIN
(
SELECT communication_id, MAX(created_at) max_time, new_status_id
FROM Communications_History
GROUP BY communication_id
) ch2
ON ch.communication_id = ch2.communication_id AND ch.created_at = ch2.max_time
SET ch.new_status_id = (
SELECT nsi FROM
(
SELECT new_status_id FROM Communications_History WHERE communication_id = ch.communication_id AND status_id IS NULL
) as ch3
)

谢谢！