Update a column with max value of multiple columns in another table

  • Roust_m

    SSCoach

    Points: 17379

    Hi,

    I have two tables, a simplified schema is like this:

    Plan (plan_id, modified_time)

    Plan_review(review_id, plan_id, eval_time, modify_time, submit_time, withdraw_time)

    All of the above columns are bigint type.   It is one to many relationship, so for one Plan record there may be multiple Plan_review records.

    I need to update modified_time column in Plan table with the MAX value of the following columns in Plan_review table: eval_time, modify_time, submit_time, withdraw_time

    The max value should be taken across all Plan_review records for a given plan_id.

    Thanks.

     

  • pietlinden

    SSC Guru

    Points: 62902

    What did you try? Something like an update against Plan using the related max from the Plan_review table?

    17K points and you ask questions with no data???

  • DesNorton

    SSC-Insane

    Points: 23322

    Without anything to test against, I would guess at a solution similar to this

    WITH cteMaxTime AS (
    SELECT pr.plan_id, maxTime = MAX( d.time_val )
    FROM Plan_review AS pr
    CROSS APPLY (
    VALUES ( pr.eval_time ), ( pr.modify_time ), ( pr.submit_time ), ( pr.withdraw_time )
    ) AS d ( time_val )
    GROUP BY pr.plan_id
    )
    UPDATE p
    SET p.modified_time = cte.maxTime
    FROM Plan AS p
    INNER JOIN cteMaxTime AS cte
    ON p.plan_id = cte.plan_id;

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply