CODEWITHSUNDEEP
sqlsql-server
user475353
user475353

Reputation:

SQL Server, Update with Counter?

I have a table called AAA. When I order table AAA by COLUMNI, there is a COLUMNJ that needs to have the values "BINXXXXX" where "BINXXXXX" needs to go up by 1 each time. For example, "BIN00001", then "BIN00002" etc.... all the way to the end. It needs to do this when I order by COLUMNI.

Is there a good way to do this?

Upvotes: 1

Views: 93

Answers (2)

Mathmagician
Mathmagician

Reputation: 113

UPDATE a
SET a.COLUMNJ = b.COLUMNJ
FROM dbo.AAA a
INNER JOIN
(SELECT COLUMNI, 
        COLUMNJ = 
'BIN' + RIGHT('0000'+ CONVERT(VARCHAR(8),ROW_NUMBER()OVER(ORDER BY COLUMNI ASC)),5) 
FROM dbo.AAA)b
ON b.COLUMNI = a.COLUMNI

Borrowing from Tim's work above, this should update your table with the correct values. Assuming that COLUMNI is unique, of course.

P.S. I don't have enough rep to comment yet, so this is going in as an answer.

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460340

SELECT COLUMNI, 
       COLUMNJ = 
'BIN' + RIGHT('0000'+ CONVERT(VARCHAR(20),ROW_NUMBER()OVER(ORDER BY COLUMNI ASC)),5) 
FROM dbo.AAA

Demo

The UPDATE part is not clear for me, however here is an example using a CTE:

WITH CTE AS (
    SELECT AAA.*, 
           BIN_Num = 'BIN' 
                   + RIGHT('0000'+ CONVERT(VARCHAR(20), ROW_NUMBER() OVER ( 
                        ORDER  BY  COLUMNI ASC)), 5) 
         FROM   dbo.AAA) 
UPDATE CTE SET COLUMNJ = BIN_Num

Demo

Upvotes: 1

Related Questions