CrossRelated Subquery - Update 2

[azwaan]

I need to update multiple coulmns in a table based on multiple from a Crossrelated subquery.

the query to update a single column is as following

UPDATE VERSION VER_ID =
SELECT MAX(VER_ID) FROM MEETING_VERSION

any ideas on how to do this to update multiple columns?

 

[Glasgow]

I'm not sure if this answers your question but this kind of approach should work:

UPDATE VERSION 
SET VER_ID = (SELECT MAX(VER_ID) FROM MEETING_VERSION),
    VER_DATE = GETDATE(),
    VER_USRER = 'azwaan'

or, I guess

UPDATE VERSION 
SET VER_ID = (SELECT MAX(VER_ID) FROM MEETING_VERSION),
    VER_DATE = (SELECT MAX(VER_DATE) FROM MEETING_VERSION)

though there may be a more efficient way of doing the latter.

 

[azwaan]

Thanks that helps..

but what if we need to update multiple columns based on the same subquery like so..

SELECT MAX(VER_ID),MET_ID FROM MEETING_VERSION GROUP BY MET_ID

since it is grouped, i need the MAX(VER_ID) for each MET_ID.
so i need to update both columns

any ideas?

 

[r937]

update VERSION
   set VER_ID = t2.MAX_VER
  from VERSION t1
inner
  join (
       SELECT MAX(VER_ID) as MAX_VER
            , MET_ID 
         FROM MEETING_VERSION 
       GROUP 
           BY MET_ID
       ) t2
    on t1.MET_ID = t2.MET_ID

r937.com | rudy.ca