Problem using Update query in Loop..

• heyzain

  Old Hand

  Points: 351

  More actions

  February 6, 2011 at 2:49 pm

  #146997

  Hi Folks,

  I need to update the value of "AssignedTo" column in my table (MITagging) to logged in user's name for all pending requests in MITagging table. For example, if there are 10 pending requests and 2 logged in users, then I want to set value of AssignedTo column for 1st 5 rows to 1st username and next 5 rows to 2nd username. I'm stuck on how to use a loop (online users may be 10 and pending requests may be 1000)

  Table MITagging:

  ----------------

  ReqID CustomerName MITaggingStatus AssignedTo

  ----- ------------ ------------- ---------

  1 Cust 1 Done NULL

  2 Cust 2 Pending NULL

  3 Cust 3 Pending NULL

  4 Cust 4 Done NULL

  5 Cust 5 Pending NULL

  Table UserRights:

  -----------------

  UserID CurrentStatus

  ------ -------------

  User1 Active

  User2 Active

  User3 InActive

  My code goes like this:

  ----------------------------------------------

  DECLARE @ActiveCount INT

  DECLARE @RequestCount INT

  DECLARE @ActiveUser VARCHAR(50)

  SELECT @ActiveCount = Count(CurrentStatus) FROM UserRights WHERE CurrentStatus = 'Active'

  SELECT @RequestCount = Count(RequestID) FROM dbo.MITagging WHERE (MITaggingStatus = 'Pending')

  If ((@ActiveCount > 0) AND (@RequestCount > 0))

  Begin

  SELECT @ActiveUser = UserID FROM UserRights WHERE CurrentStatus = 'Active'

  UPDATE TOP(@RequestCount/@ActiveCount) MITagging SET AssignedTo = @ActiveUser WHERE (MITaggingStatus = 'Pending')

  End

  -----------------------------

  This code will always pick same user name in @ActiveUser and update same rows each time we run it. Please suggest a way to pick each Active user in @ActiveUser (in a loop or some other way)

  Any help would be greatly appreciated.

  Thanks & Regards,

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 6, 2011 at 6:20 pm

  #1282912

  Here you go ...

  This generates a linking column against each table - a simple row number for each user and an NTILE based on count of users for each MITagging row...

  --= Set up some test data

  DECLARE @MITagging TABLE(

  ReqID INT

  ,CustomerName VARCHAR(50)

  ,MITaggingStatus VARCHAR(50)

  ,AssignedTo VARCHAR(50))

  INSERT @MITagging

  SELECT 1, 'Cust 1', 'Done', NULL UNION ALL

  SELECT 2, 'Cust 2', 'Pending', NULL UNION ALL

  SELECT 3, 'Cust 3', 'Pending', NULL UNION ALL

  SELECT 4, 'Cust 4', 'Done', NULL UNION ALL

  SELECT 5, 'Cust 5', 'Pending', NULL

  DECLARE @UserRights TABLE(

  UserID VARCHAR(50)

  , CurrentStatus VARCHAR(50))

  INSERT @UserRights

  SELECT 'User1','Active' UNION ALL

  SELECT 'User2','Active' UNION ALL

  SELECT 'User3','InActive' UNION ALL

  SELECT 'User4','Active' UNION ALL

  SELECT 'User5','Active'

  --= Add a few hundred test rows

  INSERT @MITagging

  SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)+5,'Cust '+CONVERT(VARCHAR,ROW_NUMBER() OVER (ORDER BY @@SPID)+5)

  ,CASE ABS(CHECKSUM(NEWID()))%2 WHEN 0 THEN 'Done' ELSE 'Pending' END,NULL

  FROM syscolumns

  --= Solution

  --= Generate row numbers for each Active user and NTILE numbers against Pending Customers

  --= Then match the two together.

  ;WITH users AS

  (

  SELECT

  ROW_NUMBER() OVER(ORDER BY UserID) AS KeyNum

  , UserID

  FROM

  @UserRights

  WHERE

  CurrentStatus = 'Active'

  ),

  mits AS

  (

  SELECT

  NTILE((SELECT COUNT(*) FROM users)) OVER(ORDER BY ReqID) AS KeyNum

  ,AssignedTo

  FROM

  @MITagging

  WHERE

  MITaggingStatus='Pending'

  )

  UPDATE mits

  SET AssignedTo = (SELECT UserID FROM users WHERE users.KeyNum = mits.KeyNum );

  SELECT * FROM @MITagging;

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]