CODEWITHSUNDEEP
sqlsql-serversql-update
Stephanie Stowe
Stephanie Stowe

Reputation: 43

SQL update query with MAX

I don't see anything that really seems the same to me on here, though it might be just me having not done sql in so long.

So I have a master table TestTempGeneralInfo and a dail TestTaskGeneralInfo. I can use this query:

SELECT testtempgeneralinfo.issueid, 
       Max(testtaskgeneralinfo.effectivetime) 
FROM   testtaskgeneralinfo 
       INNER JOIN testtempgeneralinfo 
               ON testtaskgeneralinfo.testtemplateid = 
                  testtempgeneralinfo.issueid 
WHERE  testtempgeneralinfo.projectid = 150 
GROUP  BY testtempgeneralinfo.issueid

To view the MAX of the TestTaskGeneralInfo tables effective time.

But I am struggling to write a query to update the same column TestTempGeneralInfo. Can anyone help? Hope this is not a dumb question. Thanks

Upvotes: 0

Views: 1369

Answers (2)

JosephStyons
JosephStyons

Reputation: 58685

I'm not sure I understand your question. I understand it to be:

"Given the above query, how can I update the row that contains the maximum effectivetime with new values?"

Assuming issueid is your primary key, the answer would be something like this:

update testtempgeneralinfo
set
  effectivetime = getdate()  --columns and values will vary...
where issueid = (
  SELECT top 1 testtempgeneralinfo.issueid
  FROM
    testtaskgeneralinfo 
    INNER JOIN
    testtempgeneralinfo 
    ON testtaskgeneralinfo.testtemplateid = testtempgeneralinfo.issueid 
    WHERE testtempgeneralinfo.projectid = 150 
    order by
      testtaskgeneralinfo.effectivetime desc
  );

Upvotes: 0

Jason Whitish
Jason Whitish

Reputation: 1438

There are a few different ways to do it, but the simplest for you may be to just take the query you have above and make it into a common table expression and then make your update statement join to the CTE:

    ;WITH MaxDate AS
    ( SELECT testtemplateid, Max(effectivetime) as MaxEffectiveTime
      FROM testtaskgeneralinfo
      GROUP BY testtemplateid)
    UPDATE testtempgeneralinfo 
    SET effectivetime = MaxEffectiveTime
    FROM   MaxDate 
    INNER JOIN testtempgeneralinfo 
           ON MaxDate.testtemplateid = testtempgeneralinfo.issueid 
    WHERE  testtempgeneralinfo.projectid = 150

EDIT: Sorry, had a bit of a copy/paste error there. But the above is assuming that you're wanting to update testtempgeneralinfo's effective time with the most recent testtaskgeneralinfo's effective time.

Upvotes: 1

Related Questions