CODEWITHSUNDEEP
sqlsql-server
User M
User M

Reputation: 329

How do I use for loop in SQL server

I have to update multiple rows(29) in a table in SQL server 2005.

SELECT * from tblState ORDER BY StateCode ASC.

In this table there's a integer column which I need to assign numbers starting from 1 to 29. For e.g. 

BEFORE

A 3 

B 6

C 2 
D 1 


AFTER

A 1  
B 2  
C 3  
D 4

How can I update each row with a ascending sequential number efficiently?

Upvotes: 5

Views: 672

Answers (6)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

My option without cte:

UPDATE d
SET d.integer_column = d.sequence_id
FROM (
      SELECT integer_column, ROW_NUMBER() OVER (ORDER BY StateCode ASC) AS sequence_id 
      FROM dbo.tblState
      ) d

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460058

You should avoid loops in SQL whenever possible. SQL Server is heavily optimized towards set-based operations.

In this case you can use a CTE with ROW_NUMBER function:

WITH CTE AS
(
   SELECT StateCode, IntColumn
   ,  RN = ROW_NUMBER() OVER (ORDER BY StateCode ASC)
   FROM dbo.tblState
)
UPDATE CTE SET IntColumn = RN;

Bad Habits to Kick : Thinking a WHILE loop isn't a CURSOR

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13486

try this:

with CTE1 as(select StateCode,intcol,ROW_NUMBER() over (order by StateCode) as rn from tbln)
update CTE1 set intcol=rn
select * from tbln

Upvotes: 0

Jodrell
Jodrell

Reputation: 35696

Ok, don't store the number in the table because you will have to change it every time you insert a new row.

Just do

SELECT
                ROW_NUMBER() OVER (ORDER BY StateCode ASC) sequence_id
              , *   
    FROM 
              tblState

every time you get the data out.

If you have many rows and you need to do this fast, create an index on StateCode ASC

Upvotes: 0

tozka
tozka

Reputation: 3451

You can use Common Table Expressions

with c as
(
    select ID, ROW_NUMBER() 
    over (order by StateCode ASC) as cn 
    from tblState 
)
update c set ID = cn

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

WITH
  sequenced_data AS
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY StateCode ASC) AS sequence_id,
    *
  FROM
    tblState
)
UPDATE
  sequenced_data
SET
  integer_column = sequence_id

As you are asking for a loop I guess you may not understand this code.

As such, I strongly recommend you create a dummy table to play with this, both to understand that how it works, and to ensure it does what you want/expect.

  • The WITH statement is similar to a sub query
  • In that, the ROW_NUMBER() creates the sequential ids that you want
  • The outer query then updates the column (Although it refers to the sub-query, the real table does actually get updated)

Upvotes: 5

Related Questions