使用查询的结果更新表中的行

Question

服务器：10.3.32-MariaDB

我有一个表，其中包含列ts(时间戳(、一些值和一个空列。有一个查询输出ts和我想插入到空列中的值。目标是更新查询中有相应条目(ts与ts匹配(的表。

我该如何处理整张桌子？

两次尝试均无效：

1/

UPDATE avg1h
SET pwr_peak = peaks. Peak
FROM (
SELECT ts, MAX(pwr) AS peak
FROM (
SELECT
ts
- INTERVAL EXTRACT(SECOND FROM ts) SECOND
- INTERVAL EXTRACT(MINUTE FROM ts) MINUTE AS ts,
CASE WHEN pwr < 0 THEN 0 ELSE pwr END as pwr
FROM avg15m
) AS peak15m
GROUP BY ts
) AS peaks
WHERE avg1h.ts = peaks.ts

2/

UPDATE avg1h
SET avg1h.pwr_peak = peaks. Peak
FROM avg1h
INNER JOIN (
SELECT ts, MAX(pwr) AS peak
FROM (
SELECT
ts
- INTERVAL EXTRACT(SECOND FROM ts) SECOND
- INTERVAL EXTRACT(MINUTE FROM ts) MINUTE AS ts,
CASE WHEN pwr < 0 THEN 0 ELSE pwr END as pwr
FROM avg15m
) AS peak15m
GROUP BY ts
) AS peaks
ON avg1h.ts = peaks.ts

查询(AS峰值(正确输出"ts"one_answers"peak"列。

Answer 1

答案如下：也许我被SQL方言搞混了。。。

UPDATE avg1h, (
SELECT ts, MAX(pwr) AS peak
FROM (
SELECT
ts
- INTERVAL EXTRACT(SECOND FROM ts) SECOND
- INTERVAL EXTRACT(MINUTE FROM ts) MINUTE AS ts,
CASE WHEN pwr < 0 THEN 0 ELSE pwr END as pwr
FROM avg15m
) AS peak15m
GROUP BY ts
) AS peaks
SET avg1h.pwr_peak = peaks.peak
WHERE avg1h.ts = peaks.ts AND avg1h.pwr_peak IS NULL