1:00 AM
TheEpicChocobo
Have you ever or do you need to renumber the id field in one or more of your SQL tables by their row position in the database? Well I came across needing to do that a few months back. Now I can’t actually remember why I needed to do that but I did and me being the lazy ass I am, I tried Googleing a sample script online instead of just writing my own. I couldn’t find anywhere online for a script that did it. So finally I broke down and just wrote my own and it looked a little something like this…
First you need to set all the ID fields to 1 In this example our ID field is called ItemID. You may have to remove the unique identifier property on this field to do so.
UPDATE NewsArticle SET ItemID = 1
Now that, that is done we have the script run though each item at a time so as to put them in the right order using a counter variable to keep track of the new ids. So here’s how that would look. MyTable is the name of the table we’re updating and ItemID is the ID field. The 999 is just some random max number you want it to go to before stopping it really can be anything.
DECLARE @counter INT
SET @counter = 1
WHILE @counter<999
BEGIN
UPDATE TOP (1) MyTable SET ItemID = @counter WHERE itemid=1
SET @counter = @counter + 1
END
