CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
kzhang12
kzhang12

Reputation: 211

How to update a table based on some specific condition?

I have a table, which is like below. enter image description here

I want it to be updated to below. enter image description here

Basically, per the records where A_ID<>0 and B_ID<>0, C_ID=0, if they have corresponding records which have the same A_ID and B_ID, while C_ID<>0(A_ID=1001 and A_ID=1002 in this case), set IsCurrent=0, ActiveTo to corresponding ActiveFrom - 1 second.

Thanks

Upvotes: 0

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Hmmm . . . You can identify the records as:

select t.*
from (select t.*,
             lead(ActiveFrom) over (partition by a_id, b_id order by c_id) as next_ActiveFrom
      from t
     ) t
where c_id = 0 and next_ActiveFrom is not null;

Then update them:

with toupdate as (
      select t.*
      from (select t.*,
                   lead(ActiveFrom) over (partition by a_id, b_id order by c_id) as next_ActiveFrom
            from t
           ) t
      where c_id = 0 and next_ActiveFrom is not null
     )
update toupdate
    set isactive = 0,
        activeTo = dateadd(second, -1, next_ActiveFrom);

Upvotes: 2

Related Questions