CODEWITHSUNDEEP
sql-servert-sql
variable
variable

Reputation: 9724

How to update max date of a record table based on join with another table?

I have 2 tables.

Job: jobid, jobname, jobdate

JobLog: jobid, jobtask, actiondate

I want to run a query to update the Job.jobdate with the latest value from Joblog.

Example:

SELECT JOBID, MAX(ACTIONDATE)
FROM JOBLOG
GROUP BY JOBID

Above query gives me max date per Job ID.

UPDATE J
SET J.JOBDATE=MAX(JL.ACTIONDATE)
FROM JOB J
JOIN JOBLOG JL ON J.JOBID=JL.JOBID

My confusion is that the above query will update all JobDates with the max date. How to ensure max is updated based on join?

Is the solution to write like following?

UPDATE J
SET J.JOBDATE=JL.ACTIONDATE
FROM JOB J
JOIN (SELECT JOBID, MAX(ACTIONDATE) AS ACTIONDATE
      FROM JOBLOG
      GROUP BY JOBID) JLGroupBy
ON J.JOBID=JLGroupBy.JOBID

Or the following:

;WITH CTE AS (
SELECT JOBID, MAX(ACTIONDATE) AS ACTIONDATE
          FROM JOBLOG
          GROUP BY JOBID)
UPDATE J
SET J.JOBDATE=JL.ACTIONDATE
FROM JOB 
JOIN CTE ON CTE.JOBID=JOB.JOBID

Or is there any better/cleaner approach?

Upvotes: 1

Views: 1022

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

I would express this using an updatable CTE with the help of ROW_NUMBER:

WITH cte AS (
    SELECT J.*, MAX(JL.ACTIONDATE) OVER (PARTITION BY J.JOBID) MAXACTIONDATE
    FROM JOB J
    INNER JOIN JOBLOG JL ON JL.JOBID = J.JOBID
)

UPDATE cte
SET JOBDATE = MAXACTIONDATE;

Upvotes: 1

Related Questions