Looping through a Table and setting the value for a column

people...

I'm a total SQL 开发者_运维知识库Server Newbie and have the following problem. I need to iterate over a table and update one column. In this column i want to set an Integer value. In the following row i want to add 1 to the value. So that the column rows look like this:

1

2

3

4

5

6

7

8

9

Whats the easiest way to do this?

Thanks! :-)

Are you looking for something like this?

UPDATE YourTable
    SET YourColumn = YourColumn + 1

First you need to understand SQL and set-based operations. You don't loop through a table, you update sets of information at once.

If you just need a sequential number, you could do something like this:

with cte as (select primarykey, row_number() over (order by primarykey) rn from yourtable)
update yourtable 
set yourcol = rn 
from yourtable 
join cte on yourtable.pk = cte.primarykey

But you should really understand the basics before you try writing advanced queries. If you have to ask how to (or if you can) iterate through a table, you probably shouldn't be doing it.

If you need to actually loop through (not sure that you actually do), here is a link on how you can do it 1 row at a time. The second section walks you through creating a WHILE loop:

SQL Loop