Post reply

update script

  • December 21, 2010 at 9:20 pm

    #229731

    Could someone please help me with below script

    Create table priorityupdate

    ( aid int,

    priority int,

    rowid int,

    Transaction_V nvarchar(1))

    Insert into priorityupdate(aid,priority,rowid) values(1234,2,2)

    Insert into priorityupdate(aid,priority,rowid) values(1234,3,3)

    Insert into priorityupdate(aid,priority,rowid) values(456,1,7)

    Insert into priorityupdate(aid,priority,rowid) values(456,1,5)

    Insert into priorityupdate(aid,priority,rowid) values(456,2,4)

    Insert into priorityupdate(aid,priority,rowid) values(456,3,6)

    I need a update sql script where it should update transaction_v = 'V'

    for the first record with aid= 1234 and priority = 2 and rowid = 2

    and the fourth record with aid = 456 and priority = 1 and rowid = 5

    Reason it should only update transaction_v = 'V' for first and fourth records because those are the minimum priority

    and it has the minumum rowid under each aid value

  • December 21, 2010 at 10:03 pm

    #1265353

    this will solve the problem. Here i have used rownumber to find the lowest priority and rowid.

    Declare @priorityupdate table

    ( aid int,

    priority int,

    rowid int,

    Transaction_V nvarchar(1))

    Insert into @priorityupdate(aid,priority,rowid) values(1234,2,2)

    Insert into @priorityupdate(aid,priority,rowid) values(1234,3,3)

    Insert into @priorityupdate(aid,priority,rowid) values(456,1,7)

    Insert into @priorityupdate(aid,priority,rowid) values(456,1,5)

    Insert into @priorityupdate(aid,priority,rowid) values(456,2,4)

    Insert into @priorityupdate(aid,priority,rowid) values(456,3,6)

    select * from @priorityupdate

    ;with cte1 as

    (select row_number() over(partition by aid order by priority, rowid) 'rnk', aid, priority, rowid, Transaction_v

    from @priorityupdate)

    update cte1

    set Transaction_v = 'V'

    where rnk = 1

    select * from @priorityupdate

  • December 22, 2010 at 7:14 am

    #1265506

    That was a really awesome solution Abhijeet. it worked great.

    Thanks a lot for your help Sir...it saved my couple hours

  • December 22, 2010 at 11:06 pm

    #1265778

    Thanks for the feedback.

    It was my pleasure to help.

    And no need to thank me as i have also got many solutions from this site so just doing my part to pass on the flame.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply