how to update each row using a counter in sql server-CodePudding

I'm trying to update each row in the firstName column to display FN1, FN2, FN3 etc. I know I'm missing a where clause that is causing an update to all the records. All the firstNames are different so I can't do a simple where condition i.e where firstName = 'adam'.

How can I go about updating all the rows with FN Counter?

I was made aware I can use a row_number function but was told to use the while loop first for learning purpose.

DECLARE @Counter INT 
SET @Counter = 1

WHILE (@Counter <= (Select COUNT(firstname) FROM CONSTITUENT_TEST))
BEGIN
    UPDATE CONSTITUENT_TEST
    SET firstname ='FN'   CONVERT(VARCHAR,@Counter)
    SET @Counter = @Counter    1
END
select * from CONSTITUENT_TEST

CodePudding user response：

If you had to do this with a WHILE loop, you could use an UPDATE with a TOP (1) and check that a row was updated in the prior UPDATE statement like so:

DECLARE @Counter int = 0;

WHILE @@ROWCOUNT > 0 OR @Counter = 0 BEGIN
    SET @Counter = @Counter   1;

    UPDATE TOP(1) dbo.YourTable
    SET Firstname = CONCAT('FN',@Counter)
    WHERE Firstname NOT LIKE 'FN[0-9]%';
END;
GO

Honestly though, like mentioned in the comments, I would suggest using a set based solution, it'll it far more performant. You can achieve this with an updatable Common Table Expression (CTE) and ROW_NUMBER:

WITH CTE AS(
    SELECT Firstname,
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN --SELECT NULL for arbitrary order. You can use a specific one if you want to
    FROM dbo.YourTable)
UPDATE CTE
SET Firstname = CONCAT('FN',RN);

CodePudding user response：

This is just a variation of what Larnu posted. It uses the primary key (assumed to be the column ID) of your table to iterate through the rows.

DECLARE @Counter int = 0;
DECLARE @ID int;

SET @ID = (select min(ID) from dbo.YourTable); // assuming ID is the primary key for your table
IF @ID IS NULL RETURN; // maybe throw an error? Table is empty

WHILE @ID IS NOT NULL BEGIN
    SET @Counter = @Counter   1;

    UPDATE dbo.YourTable
    SET Firstname = CONCAT('FN', @Counter)
    WHERE ID = @ID; 

    SET @ID = (select min(ID) from dbo.YourTable where ID > @ID); 
END;
GO

You could easily convert this into a cursor if you want to explore that option as well. As I wrote earlier - if you need a cursor then just use one. But rarely do you need one.

CodePudding user response：

Try this:

DECLARE @Counter INT 
SET @Counter = 1

WHILE (@Counter <= (Select COUNT(firstname) FROM CONSTITUENT_TEST))
BEGIN
    UPDATE CONSTITUENT_TEST
    SET firstname ='FN'   CONVERT(VARCHAR,@Counter)
    SET @Counter = firstname = @Counter    1
END
select * from CONSTITUENT_TEST