HI All,

I have a table as claim_data as below.

claim_id    amount         change_monthkey          created_monthkey          ord

 

54511       300            201304                   201304                     1

54511       0              201305                   201304                     2

120301      250            201502                   201502                     1

120624      150            201502                   201502                     1

120624      0              201503                   201502                     2

I want to isolate rows which appear in the table and do a update using below query ( This query is already in a procedure, so I just looking for a suitable way to modify it to support my task )

In the above e.g.  ONLY row containing claim_id = 120301 needs to update (amount  as updated_amount)

I use following query, but it update other rows as well, I want a suitable query only to capture rows of nature claim_id = 120301 and do this update? Is there any functions that I could use to facilitate my task??

select
     a.claim_id
    , a.Created_MonthKey
    , a.Change_MonthKey
    , a.amount - ISNULL(b.amount,0.00) as amount_movement
    ,a.amount  as updated_amount   --- >> I need help here??
    FROM claim_data a
LEFT JOIN claim_data b
    ON a.claim_id = b.claim_id
    AND b.Ord = a.Ord - 1

Thanks

Mira

• Edited by Mira Pimbi 1 hour 31 minutes ago to clear some details

As you need a conditional update statement, i am assuming that you have only one row whose claim_id is 120301 and ORD=1.

UPDATE claim_data SET amount = 12345 WHERE claim_id = 120301 and Ord = 1