Update a column from a SELECT of another table

Question

I have been attempting to update a column based on a group by a select from one table into another table. The below subquery on the set statement works but only for one date because if I use a date range I get an error of "subquery returns more than 1 row".

I instead want to run that on a date range fetching the group by for each day (from "Monthly" table) inserting each matching row by day into "Dayfile" table. The dayfile table has a row for each date with the "LogDate column" as date and the monthly table is a log file of minute-by minute values where the "LogDateTime" data type is datetime.

UPDATE
    Dayfile
SET
    MaxFeelsLike = 
         (SELECT MAX(FeelsLike)
          FROM Monthly, Dayfile
          WHERE DATE(LogDateTime) = "2018-10-04"
          AND DATE(LogDateTime) = DATE(LogDate)
          GROUP BY DATE(LogDateTime)
         );

score 0 · Answer 1 · answered May 10 '20 at 05:10

0

Include LIMIT 1 at the end of your subquery

answered May 10 '20 at 05:10

danblack

12,130
2
22
41

Barmar · Accepted Answer · 2020-05-10T13:20:32.237

0

You should use a JOIN rather using the subquery as a value.

UPDATE Dayfile AS d
JOIN (
    SELECT DATE(LogDateTime) AS date, MAX(FeelsLike) AS feels
    FROM Monthly
    GROUP BY date
) AS m ON DATE(d.LogDate) = m.date
SET d.MaxFeelsLike = m.feels

edited May 10 '20 at 13:20

answered May 10 '20 at 05:19

Barmar

741,623
53
500
612

That works fine. I would also like to return the corresponding value of "LogDateTime" of the Max(FeelsLike) value for each date GROUP BY. Changing the set statement to update the column holding the time for MaxFeelsLike ("TMaxFeelsLike") returns the first value (date 00:00) in the GROUP BY which I don't want. – Matthew May 10 '20 at 06:41
See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the whole row with the max value, not just the max value by itself. – Barmar May 10 '20 at 13:19

Update a column from a SELECT of another table

2 Answers2