SQL update column in row based on row number-CodePudding

I am trying to sanitize a database I have for testing purposes, and want to update a column (name) to something like Name <rownumber>.

I have given it a go myself, but it cycles through all the rows and ends up naming them all the same at the end Name 323; 323 being the total number of rows.

Some direction would be very helpful.

This is what I have at the moment

Should I not be doing a while for each row in the table?

DECLARE @counter INT = 2
DECLARE @rows int
DECLARE @CustCode varchar(20)
DECLARE @CustName varchar(128)
DECLARE @CustID varchar(10) 

SELECT @rows = COUNT(*) FROM Customer

WHILE @counter <= @rows
BEGIN
    SELECT @CustID = CustomerID FROM Customer
    SET @CustCode = 'CustCode'   @CustID
    SET @CustName = 'Customer Description '   @CustID
        
    SET @CustName = 'Customer Description '   @CustID

    UPDATE Customer 
    SET CustomerCode = @CustCode, 
        CustomerDescription = @CustName

    SET @counter = @counter   1
END

CodePudding user response：

Really, it would be much better to use ONE, set-based statement for this. You're not using any specific additional information (like a row number - as mentioned in your post) - you're just concatenating fixed text like Cust Code and Customer Description) with the already existing CustomerId - so a simple UPDATE statement like this would do (and would update everything in one go):

UPDATE dbo.Customer
SET CustomerCode = CONCAT('Cust Code ', CustomerId),
    CustomerDescription = CONCAT('Customer Description ', CustomerId);