SQL: UPDATE by SELECT – marioosh.net

Need update t_reports table, column tanked_sum.

t_reports – reports table
- tanked_sum – sum of tanked from t_vehicles table
t_vehicles – vehicles table (more than one vehicle is in one report)
- tanked – refueled liters of fuel for one vehicle

-- select only (count in `tanked_sum_counted` column)
SELECT
    tanked_sum_counted,
    *
FROM
    t_reports r
    JOIN (
        SELECT
            SUM(tanked) tanked_sum_counted,
            report_id
        FROM
            t_vehicles
        GROUP BY
            report_id
    ) s ON s.report_id = id
 
-- update `t_reports.tanked_sum` from counted `tanked_sum_counted` in table `t_vehicles`
UPDATE
    t_reports
SET
    tanked_sum = s.tanked_sum_counted
FROM
    t_reports r
    JOIN (
        SELECT
            SUM(tanked) tanked_sum_counted,
            report_id
        FROM
            t_vehicles
        GROUP BY
            report_id
    ) s ON s.report_id = id