Geek asks and answers

SQL Server: UPDATE statement where the MAX query

I am doing data migration in SQL Server 2008 R2. I am a noob SQL server, but I know Ingres and MySql well.

I need to set the "default values" for two new fields to the "current values" from another table. Here is my first naive attempt (as I would have done in Ingra).

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 is executed without errors in the future, but it sets ALL parameters n_target_probability_ID and n_target_consequence_ID to the same value ... as for the last OUTRIGHT score (as indicated to the "last assessment of THIS RISK").

The table rk_assesscontains the full history of the score records for rk_risks, and my mission is to “default” a new probability column and the consequences of the risk table on the values ​​from the “current” (ie last) score. The column rk_assess.n_assess_idis an automatically incrementing identifier (unchanged after installation), so the maximum identifier should always be the last entry entered.

I had a search, both in google and SO, and tried several different versions of the request, but I was still stuck. Here are a couple of other epic setbacks, with links.

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. Thanks to everyone in advance for even reading this far.

Greetings. Whale.

+3
source share
4 answers

:

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
+6

sql 2005 , row_number

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

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
+3

, , :

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)
+2

SO . UPDATE-FROM SQL, MySQL Postgres. , , SQL Server Postgres.

, , . , MySQL - ( ? SO, , ).

I don’t know if SQL Server allows windows to be made in a subquery, but if so, 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;
+1
source

More articles:


All Articles