CODEWITHSUNDEEP
sqlsql-server
Zach
Zach

Reputation: 1321

How can I iterate through SQL results like a for loop and an array?

I have a table, and I want to select only the single column of row IDs from it, but in a specific order. Then, I want to loop through that column like below:

for (i=0; i<rows.length; i++)
{
  if(i==rows.length-1)
    UPDATE myTable SET nextID = NULL WHERE ID = rows[i]
  ELSE
    UPDATE myTable SET nextID = rows[i+1] WHERE ID = rows[i]
}

I just dont know how to access the results of my select statement with an index like that. Is there a way of doing this in sql server?

Upvotes: 0

Views: 1098

Answers (2)

sstan
sstan

Reputation: 36493

Since you didn't provide many details, let's pretend your table looks something like this:

create table MyTable (
  Id int not null primary key,
  Name varchar(50) not null,
  NextId int
)

I want to select only the single column of row IDs from it, but in a specific order

Let's just say that in this case, you decide to order the rows alphabetically by Name. So let's pretend that the select statement that you want to loop through looks like this:

select Id
  from MyTable
 order by Name

That being the case, instead of looping through the rows and attempting to update each row using the pseudo-code you provided, you can replace the whole thing with a single update statement that will perform the exact same work:

with cte as (
  select *,
         NewNextId = lead(Id) over (order by Name)
    from MyTable
)
update cte
   set NextId = NewNextId

Just make sure to adjust the order by clause to whatever your specific order really is. I just used Name in my example, but it might be something else in your case.

Upvotes: 1

LordBaconPants
LordBaconPants

Reputation: 1414

You could use a cursor, or you could use something a bit smarter.

Your example should be able to be written fairly easily along the lines of:

update mytable set nextID = LEAD(id,1) over (order by id)

Lead(id,1) will grab the next id, 1 row ahead in the record set and update the nextID field with it. If it can't find one it will return null. No looping or conditional logic needed!

edit: I forgot the over clause. This is the part that tells it how you would like it ordered for the lead

Upvotes: 1

Related Questions