here's an example of how you can perform a variable assignment in an sql update statement. each row processed by the update *also* performs the assignment.
the key is to use this syntax...
SET @x = MyIntegerColumn = @x + 1
this example just increments a value - so each row updated gets a higher value (most cases you would use an index for such a task).
-- create a table:
USE tempdb
GO
CREATE TABLE MyTable (
MyTableID int NULL,
[Name] varchar(35) NOT NULL
)
-- populate some data
INSERT MyTable ([Name]) VALUES ('Jamie')
INSERT MyTable ([Name]) VALUES ('David')
INSERT MyTable ([Name]) VALUES ('Harold')
GO
-- populate MyTableID
DECLARE @vKeyCounter int
SET @vKeyCounter = (SELECT ISNULL(MAX(MyTableID), 0) FROM MyTable)
UPDATE MyTable
SET @vKeyCounter = MyTableID= @vKeyCounter + 1
WHERE MyTableID IS NULL
OPTION (MAXDOP 1)
-- View the results
SELECT *
FROM MyTable