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

• Roust_m

  SSCoach

  Points: 17379

  More actions

  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.

   

• pietlinden

  SSC Guru

  Points: 62902

  More actions

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

• DesNorton

  SSC-Insane

  Points: 23322

  More actions

  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.