I have to update a counter field in one table while pulling the next counter number from another table and update that table. For example:
Table 1) SYSCNTRL has a field called INDXCNTR
Table 2) ITEMMSTR has item numbers and needs the next available INDXCNTR from SYSCNTRL. This field is also called INDXCNTR
So as I loop through records in ITEMSTR I need to get the value of SYSCNTRL.INDXCNTR and assign it to this record. Immediately afterward I need to increment the value of SYSCNTRL.INDXCNTR by one. Then I continue the process until all records are updated.
I have done this before with cursors but I am trying to move away from using cursors.
This is for SQL Server 2005.
BEGIN TRANSACTION
DECLARE @rows_updated int
UPDATE i
SET INDXCNTR = s.INDXCNTR + i_keys.row_num
FROM (
SELECT key_col, /*key_col2, ... */
ROW_NUMBER() OVER(ORDER BY key_col) AS row_num
FROM dbo.ITEMMSTR
--WHERE <conditions_indicating_IND
) AS i_keys
INNER JOIN dbo.ITEMMSTR i ON
i.key_col = i_key.key_col
-- WITH (TABLOCKX) is required -- do NOT remove it!
CROSS JOIN dbo.SYSNCTRL s WITH (TABLOCKX)
SET @rows_updated = @@ROWCOUNT
UPDATE dbo.SYSNCTRL
SET INDXCNTR = INDXCNTR + @rows_updated
COMMIT TRANSACTION --this will release the TABLOCKX
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
--...other error logic here...
END CATCH
END CATCH