SQL Server: UPDATE statement where MAX query

Question

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 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.

Does it have to be done in one statement? – WOPR Jun 17 '11 at 04:42 — WOPR, Jun 17 '11 at 04:42

score 6 · Accepted Answer · edited Jun 17 '11 at 05:17

6

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

edited Jun 17 '11 at 05:17

corlettk

13,288
7
38
52

answered Jun 17 '11 at 04:47

Jerad Rose

15,235
18
82
153

The assessment joins to the "parent" rk_risk on `rk_assess.n_risk_id` – corlettk Jun 17 '11 at 04:57
@corlettk - Try the update above, I believe that will work. The key is you need a derived table (b) that returns to you the last assessment for each n_risk_id, then you can join rk_assess on the PK to get the associated data. Make sense? – Jerad Rose Jun 17 '11 at 05:01
I tried your fist cut, and it had the same behaviour as my first attempt... i.e. all row got values from the abolutute-last assessment... I tried the modified version, and **IT WORKS**!!! Thank you soooooo much. I've been pretty frustrated, that something I thought would be easy, and therefore didn't estimate any time for, turned out to be sooooo tricky. Thanx again. Keith. – corlettk Jun 17 '11 at 05:14
@corlettk - Awesome, glad it worked out for you! Also, take note of the answers below by @Andrew Lazarus and @Conrad Frix, using `row_number()`, which is a more elegant solution if you have SQL Server 2005+. That comes in handy especially when you need to get a certain row based on criteria other than the min/max PK. In your case, you just needed the max PK value for your join, so both solutions will work in that case. – Jerad Rose Jun 17 '11 at 05:20

Conrad Frix · Answer 2 · 2011-06-17T05:14:43.453

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 like that second version... using "select first from order by" has allways seemed "more natural" to me than "x = max(x) where rest-of-the-where-clause"... which is really cumbersome when there's (logically) several fields in the "order by" clause. — corlettk, Jun 17 '11 at 05:20

score 2 · Answer 3 · edited Jun 17 '11 at 05:24

2

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)

edited Jun 17 '11 at 05:24

corlettk

13,288
7
38
52

answered Jun 17 '11 at 05:12

Aziz Shaikh

16,245
11
62
79

1

Thank You for answering... it's a team thing ;-) – corlettk Jun 18 '11 at 08:08

score 1 · Answer 4 · answered Jun 17 '11 at 05:08

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;

Thank You! That's yet another variant of Conrads first solution. I thought I knew SQL, but I'm quickly learning that I've got a lot to learn. — corlettk, Jun 17 '11 at 05:27
@CorlettK: I'm just beginning with windowing and CTEs myself. I think they aren't getting enough attention because MySQL doesn't support them. — Andrew Lazarus, Jun 17 '11 at 05:36

SQL Server: UPDATE statement where MAX query

4 Answers4