update script

• Lucky9

  Default port

  Points: 1470

  More actions

  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

• Abhijeet Chavan

  SSCommitted

  Points: 1511

  More actions

  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

• Lucky9

  Default port

  Points: 1470

  More actions

  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

• Abhijeet Chavan

  SSCommitted

  Points: 1511

  More actions

  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.