英文:
Update table with join using maximum datetime for certain field
问题
你可以尝试使用子查询来解决这个问题。以下是一个可能的解决方案:
UPDATE boxes
JOIN (
SELECT number, MAX(meas_date) AS max_meas_date
FROM containers_measure
GROUP BY number
) AS subquery
ON boxes.number = subquery.number
JOIN containers_measure
ON boxes.number = containers_measure.number AND subquery.max_meas_date = containers_measure.meas_date
SET boxes.power = containers_measure.P_Si;
这个SQL查询会首先创建一个子查询,找到每个number
对应的最大meas_date
。然后,它将这个子查询与boxes
表和containers_measure
表进行连接,确保number
匹配并且meas_date
也匹配。最后,它会将containers_measure
表中找到的最后一次测量的P_Si
值赋给boxes
表的power
字段。
请注意,这个查询假设每个number
在containers_measure
表中都有至少一条对应的记录。如果有可能存在没有匹配记录的number
,则需要进一步处理。
英文:
I have a table containers_measure
:
+-----+--------+---------------------+----------------------+
| id | number | meas_date | P_Si |
+-----+--------+---------------------+----------------------+
| 549 | 22 | 2023-05-21 11:34:05 | 0.010040000081062317 |
| 550 | 15 | 2023-05-21 11:34:05 | 0.009720000438392162 |
| 551 | 18 | 2023-05-21 11:34:05 | 0.009859999641776085 |
| 552 | 16 | 2023-05-21 11:34:05 | 0.009850000031292439 |
| 553 | 31 | 2023-05-21 11:34:05 | 0.00965999998152256 |
| 554 | 23 | 2023-05-21 11:34:05 | 0.009960000403225422 |
| 555 | 6 | 2023-05-21 11:34:05 | 0.0102300001308322 |
| 556 | 5 | 2023-05-21 11:34:05 | 0.010200000368058681 |
| 557 | 4 | 2023-05-21 11:34:05 | 0.010169999673962593 |
| 558 | 13 | 2023-05-21 11:34:05 | 0.010099999606609344 |
| 559 | 36 | 2023-05-21 11:34:05 | 0.009829999879002571 |
| 560 | 3 | 2023-05-21 11:34:05 | 0.010099999606609344 |
| 561 | 2 | 2023-05-21 11:34:05 | 0.010180000215768814 |
| 562 | 1 | 2023-05-21 11:34:05 | 0.0102399997413158 |
| 563 | 32 | 2023-05-21 11:34:05 | 0.010230000130832195 |
| 564 | 30 | 2023-05-21 11:34:05 | 0.010250000283122063 |
| 565 | 27 | 2023-05-21 11:34:05 | 0.009960000403225422 |
| 566 | 8 | 2023-05-21 11:34:05 | 0.010040000081062317 |
| 567 | 7 | 2023-05-21 11:34:05 | 0.010049999691545963 |
| 568 | 33 | 2023-05-21 11:34:05 | 0.010049999691545963 |
| 569 | 10 | 2023-05-21 11:34:05 | 0.0101500004529953 |
| 570 | 26 | 2023-05-21 11:34:05 | 0.010180000215768814 |
| 571 | 25 | 2023-05-21 11:34:05 | 0.010099999606609344 |
| 572 | 24 | 2023-05-21 11:34:05 | 0.010040000081062317 |
| 573 | 17 | 2023-05-21 11:34:05 | 0.009650000371038914 |
| 574 | 14 | 2023-05-21 11:34:05 | 0.00977999996393919 |
| 575 | 12 | 2023-05-21 11:34:05 | 0.009879999794065952 |
| 576 | 11 | 2023-05-21 11:34:05 | 0.00953999999910593 |
| 577 | 21 | 2023-05-21 11:34:05 | 0.009700000286102295 |
| 578 | 9 | 2023-05-21 11:34:05 | 0.009850000031292439 |
| 579 | 28 | 2023-05-21 11:34:05 | 0.00977999996393919 |
| 580 | 19 | 2023-05-21 11:34:05 | 0.009809999726712704 |
| 612 | 22 | 2023-05-21 11:38:11 | 0.010040000081062317 |
| 613 | 15 | 2023-05-21 11:38:11 | 0.009720000438392162 |
| 614 | 18 | 2023-05-21 11:38:11 | 0.009859999641776085 |
| 615 | 16 | 2023-05-21 11:38:11 | 0.009850000031292439 |
| 616 | 31 | 2023-05-21 11:38:11 | 0.00965999998152256 |
| 617 | 23 | 2023-05-21 11:38:11 | 0.009960000403225422 |
| 618 | 6 | 2023-05-21 11:38:11 | 0.0102300001308322 |
| 619 | 5 | 2023-05-21 11:38:11 | 0.010200000368058681 |
| 620 | 4 | 2023-05-21 11:38:11 | 0.010169999673962593 |
| 621 | 13 | 2023-05-21 11:38:11 | 0.010099999606609344 |
| 622 | 36 | 2023-05-21 11:38:11 | 0.009829999879002571 |
| 623 | 3 | 2023-05-21 11:38:11 | 0.010099999606609344 |
| 624 | 2 | 2023-05-21 11:38:11 | 0.010180000215768814 |
| 625 | 1 | 2023-05-21 11:38:11 | 0.0102399997413158 |
| 626 | 32 | 2023-05-21 11:38:11 | 0.010230000130832195 |
| 627 | 30 | 2023-05-21 11:38:11 | 0.010250000283122063 |
| 628 | 27 | 2023-05-21 11:38:11 | 0.009960000403225422 |
| 629 | 8 | 2023-05-21 11:38:11 | 0.010040000081062317 |
| 630 | 7 | 2023-05-21 11:38:11 | 0.010049999691545963 |
| 631 | 33 | 2023-05-21 11:38:11 | 0.010049999691545963 |
| 632 | 10 | 2023-05-21 11:38:11 | 0.0101500004529953 |
| 633 | 26 | 2023-05-21 11:38:11 | 0.010180000215768814 |
| 634 | 25 | 2023-05-21 11:38:11 | 0.010099999606609344 |
| 635 | 24 | 2023-05-21 11:38:11 | 0.010040000081062317 |
| 636 | 17 | 2023-05-21 11:38:11 | 0.009650000371038914 |
| 637 | 14 | 2023-05-21 11:38:11 | 0.00977999996393919 |
| 638 | 12 | 2023-05-21 11:38:11 | 0.009879999794065952 |
| 639 | 11 | 2023-05-21 11:38:11 | 0.00953999999910593 |
| 640 | 21 | 2023-05-21 11:38:11 | 0.009700000286102295 |
| 641 | 9 | 2023-05-21 11:38:11 | 0.009850000031292439 |
| 642 | 28 | 2023-05-21 11:38:11 | 0.00977999996393919 |
| 643 | 19 | 2023-05-21 11:38:11 | 0.009809999726712704 |
+-----+--------+---------------------+----------------------+
and a table boxes
:
+----+--------+----------------------+
| id | number | power |
+----+--------+----------------------+
| 8 | 22 | 0.010040000081062317 |
| 9 | 15 | 0.009720000438392162 |
| 12 | 18 | 0.009859999641776085 |
| 13 | 16 | 0.009850000031292439 |
| 14 | 31 | 0.00965999998152256 |
| 16 | 23 | 0.009960000403225422 |
| 17 | 6 | 0.0102300001308322 |
| 18 | 5 | 0.010200000368058681 |
| 19 | 4 | 0.010169999673962593 |
| 20 | 13 | 0.010099999606609344 |
| 21 | 36 | 0.009829999879002571 |
| 22 | 3 | 0.010099999606609344 |
| 23 | 2 | 0.010180000215768814 |
| 24 | 1 | 0.0102399997413158 |
| 25 | 32 | 0.010230000130832195 |
| 26 | 30 | 0.010250000283122063 |
| 27 | 27 | 0.009960000403225422 |
| 29 | 8 | 0.010040000081062317 |
| 30 | 7 | 0.010049999691545963 |
| 31 | 33 | 0.010049999691545963 |
| 32 | 10 | 0.0101500004529953 |
| 33 | 26 | 0.010180000215768814 |
| 34 | 25 | 0.010099999606609344 |
| 35 | 24 | 0.010040000081062317 |
| 36 | 17 | 0.009650000371038914 |
| 37 | 14 | 0.00977999996393919 |
| 38 | 12 | 0.009879999794065952 |
| 39 | 11 | 0.00953999999910593 |
| 42 | 21 | 0.009700000286102295 |
| 53 | 9 | 0.009850000031292439 |
| 54 | 28 | 0.00977999996393919 |
| 55 | 19 | 0.009809999726712704 |
+----+--------+----------------------+
I need to put the last measured P_Si
from containers_measure
to the field power
in the table boxes
with corresponding number
. The datetime from containers_measure
should be maximal for the given number
. I tried this:
update boxes join containers_measure using(number)
set boxes.power=containers_measure.P_Si
group by containers_measure.number
having meas_date=max(meas_date);
but since it is impossible to use having
in an update
statement with join
, it causes an error.
How can I solve this problem?
答案1
得分: 1
在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;
英文:
On MySQL 8+, we can express your update as an inner join with the help of ROW_NUMBER
:
<!-- language: sql -->
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;
On earlier versions of MySQL, we would have to go with a pure join approach:
<!-- language: sql -->
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论