Updating tables based on a SELECT statement

• Jim-158436

  Grasshopper

  Points: 23

  More actions

  March 8, 2005 at 11:46 am

  #162886

  I need to make a large amount of data edits to a database. I have an application that assigns a unique code to users. I do a SELECT statement to pull out the user codes that I need to change. Based on the results of that SELECT Statement,I need to compare the user codes and UPDATE 8 other tables.

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  March 8, 2005 at 12:23 pm

  #545983

  Since your codes are unique, you should be able to do a loop. 

  Delcare the variables @CurrentID, @MaxID, @NewID

  SELECT @CurrentID = (SELECT MIN( ID) FROM Table)

  SELECT @MaxID = (SELECT MAX( ID) FROM Table)

  WHIILE @CurrentID <= @MaxID

  BEGIN

       MAKE YOUR @NewID EQUAL WHATEVER CHANGES YOU WANT TO YOUR @CurrentID (the unique code you refered to).

       Do your table manipulations here using the @CurrentID to match records...

  SELECT @CurrentID = (SELECT MIN( ID) FROM Table WHERE ID > @CurrentID)     

  END

  I wasn't born stupid - I had to study.