September 18, 2020 at 4:03 am
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
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
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy