Post reply

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

  • September 18, 2020 at 4:03 am

    #3789301

    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.

     

  • September 18, 2020 at 4:40 am

    #3789305

    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???

  • September 18, 2020 at 5:31 am

    #3789310

    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;

    How to post data/code on a forum to get the best help.

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

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