SQL Server: UPDATE statement where MAX query

I'm doing a data migration in SQL Server 2008 R2. I'm a SQL-Server noob, but I know Ingres and MySql pretty well.

I need to set "default values" for two new fields to "the current values" from another table. Here's my first naive attempt (how I'd do it in Ingres).

update  rk_risk
set     n_target_probability_ID = a.n_probability_ID
      , n_target_consequence_ID = a.n_consequence_ID
from    rk_assess a
WHERE   a.n_assess_id = (
    SELECT  MAX(n_assess_id)
    FROM    rk_assess a2
    WHERE   a2.n_risk_id = a.n_risk_id
);

The above query executes without error in sequel, but it sets ALL the n_target_probability_ID's & n_target_consequence_ID's to the same value... that of the OUTRIGHT last assessment (as apposed to "the last assessment OF THIS RISK").

The rk_assess table contains a complete history of assessment records for rk_risks, and my mission is to "default" the new target probability & consequence column of the risk table to the values from "the current" (i.e. the last) assessment record. The rk_assess.n_assess_id column is an auto-incremented identifier (immutable once set), so the max-id should allways be the last-entered record.

I've had a bit of a search, both in google and SO, and tried a few different version of the query, but I'm still stuck. Here's a couple of other epic-fails, with references.

update  rk_risk
set     n_target_probability_ID = (select a.n_probability_ID from rk_assess a where a.n_assess_id = (select max(n_assess_id) from rk_assess a2 where a2.n_risk_id = a.n_risk_id) as ca)
      , n_target_consequence_ID = (select a.n_consequence_ID from rk_assess a w开发者_开发知识库here a.n_assess_id = (select max(n_assess_id) from rk_assess a2 where a2.n_risk_id = a.n_risk_id) as ca)
;

http://stackoverflow.com/questions/6256844/sql-server-update-from-select

update  r 
set     r.n_target_probability_ID = ca.n_probability_ID
      , r.n_target_consequence_ID = ca.n_consequence_ID
from    rk_risk r
join    rk_assess a
on      a.n_risk_id = r.n_risk_id

select  r.n_risk_id
          , r.n_target_probability_ID, r.n_target_consequence_ID
          , ca.n_probability_ID, ca.n_consequence_ID
from    rk_risk r
join    rk_assess a
on      a.n_risk_id = r.n_risk_id

http://stackoverflow.com/questions/4024489/sql-server-max-statement-returns-multiple-results

UPDATE  rk_risk
SET     n_target_probability_ID = ca.n_probability_ID
      , n_target_consequence_ID = ca.n_consequence_ID
FROM    ( rk_assess a
INNER JOIN (
       SELECT MAX(a2.n_assess_id)
       FROM   rk_assess a2
       WHERE  a2.n_risk_id = a.n_risk_id
) ca -- current assessment

Any pointers would be greatly appreciated. Thank you all in advance, for even reading this far.

Cheers. Keith.

---

How about this:

update  rk_risk
set     n_target_probability_ID = a.n_probability_ID
      , n_target_consequence_ID = a.n_consequence_ID
from    rk_assess a
JOIN    (
    SELECT  n_risk_id, MAX(n_assess_id) max_n_assess_id
    FROM    rk_assess
    GROUP BY n_risk_id
    ) b
ON  a.n_risk_id = b.n_risk_id AND a.n_assess_id = b.max_n_assess_id
WHERE   a.n_risk_id = rk_risk.n_risk_id

---

if you're using sql 2005 or greater you can in addition to Jerad's answer use the row_number function

With b
(
         SELECT  n_risk_id, 
                 n_assess_id,
                 n_probability_ID,
                 n_consequence_ID,
                 row_number() over (partition by n_risk_id order by n_assess_id desc) row
      FROM    rk_assess
)   
update  rk_risk
set     n_target_probability_ID = b.n_probability_ID
      , n_target_consequence_ID = b.n_consequence_ID
from    b
WHERE   a.n_risk_id = rk_risk.n_assess_id
         and row =1 

Or CROSS JOIN

update  rk_risk
set     n_target_probability_ID = b.n_probability_ID
      , n_target_consequence_ID = b.n_consequence_ID
from    rh_risk r
      CROSS JOIN
      (
         SELECT  TOP 1
                 n_risk_id, 
                 n_assess_id,
                 n_probability_ID,
                 n_consequence_ID
         FROM    rk_assess
         order by n_assess_id desc
         WHERE   a.n_risk_id = r.n_assess_id)  b 

---

I tried this, looks like it is working:

update  rk_risk
set     n_target_probability_ID = a.n_probability_ID,
        n_target_consequence_ID = a.n_consequence_ID
from    rk_assess a, rk_risk r
WHERE   a.n_risk_id = r.n_risk_id
and a.n_assess_id in (select MAX(n_assess_id) from rk_assess group by n_risk_id)

---

I discovered this from another question on SO just today. The UPDATE-FROM construction is not standard SQL, and MySQL's non-standard version is different from Postgres's non-standard version. From the problem here, it looks like SQL Server follows Postgres.

The problem, as Jerad points out in his edit, is that there is no link between the table being updated and the tables in the subquery. MySQL seems to create some implicit join here (on column names? in the other SO example, it was by treating two copies of the same table as the same, not separate).

I don't know if SQL Server allows windowing in the subquery, but if it does, I think you want

UPDATE  rk_risk
set     n_target_probability_ID = a.n_probability_ID
      , n_target_consequence_ID = a.n_consequence_ID
from
  ( SELECT * FROM
     ( SELECT n_risk_id, n_probability_ID, n_consequence_ID,
              row_number() OVER (PARTITION BY n_risk_id ORDER BY n_assess_ID DESC) AS rn
       FROM rk_assess)
    WHERE rn = 1) AS a
WHERE a.n_risk_id=rk_risk.n_risk_id;