Require OVER() clause to use ROW_NUMBER().

SELECT 
			CustomerId, ROW_NUMBER() OVER(ORDER BY CustomerId ASC) AS RowNumber 
		FROM 
			dbo.tblCustomer

Have to use subquery to update row using ROW_NUMBER().

UPDATE	
	c2
SET
	c2.CustomerCode = c1.RowNumber
FROM
	(
		SELECT 
			CustomerId, ROW_NUMBER() OVER(ORDER BY CustomerId ASC) AS RowNumber 
		FROM 
			dbo.tblCustomer
	) c1
	INNER JOIN dbo.tblCustomer c2 ON c1.CustomerId = c2.CustomerId
Last modified: July 28, 2019

Author

Comments

Write a Reply or Comment