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_risk`s, 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 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)
	;
	
	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.
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_risk`s, 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 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)
	;
	
	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.