在MySQL 8+上,我们可以在的帮助下将您的更新表示为内部联接
ROW_NUMBER
:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY number ORDER BY meas_date DESC) rn
FROM containers_measure
)
UPDATE boxes b
INNER JOIN cte t ON t.number = b.number
SET b.power = t.P_Si
WHERE rn = 1;
在早期版本的MySQL上,我们必须使用纯联接方法:
UPDATE boxes b
INNER JOIN containers_measure c1
ON c1.number = b.number
INNER JOIN
(
SELECT number, MAX(meas_date) AS max_meas_date
FROM containers_measure
GROUP BY number
) c2
ON c2.number = c1.number AND
c2.max_meas_date = c1.meas_date
SET
b.power = c1.P_Si;
