CODEWITHSUNDEEP
sql-servert-sql
Diego Sendra
Diego Sendra

Reputation: 13

T-SQL WHILE (SELECT ())

I am trying to do something that would take me less than 1 minute doing it in any language. I am trying to "sort of" iterate a table in a store procedure, but to my surprise it's not possible.

So I am trying to create a store procedure, just for testing/learning purposes, using the while ((SELECT)) approach in a way to update certain rows, from range 96811 to 96815 (the max(id) is something like 96830).

Ok, this is what I am trying:

while ((select min(id) from parser) <= 96815)
    begin
        update parser set tempID=id+1 where id<=96815
        if ((select max(tempID) from parser)>=96815)
            break
    end

Now, the problem is that the while() is not doing what I want, which is basically getting the first N rows up to id=96815. Then the UPDATE should work with that subset only.

At this point, just the UPDATE line by itself would do the trick and work enough.

So is there a way to do what I want in a sort of what you would do through any language, getting a recordset, iterating it, updating what you need and exiting?

I took this approach from something posted in MSDN:

WHILE ((SELECT AVG(ListPrice) FROM dbo.DimProduct) < $300)
    BEGIN
        UPDATE DimProduct
                    SET ListPrice = ListPrice * 2;
        IF ((SELECT MAX(ListPrice) FROM dbo.DimProduct) > $500)
    BREAK;
END

But looks like in their example it won't either work.

Upvotes: 1

Views: 375

Answers (2)

Gauravsa
Gauravsa

Reputation: 6524

You can also do this:

update parser set tempID=id+1
   where id in (select id from table where condition...)

Upvotes: 0

Tyler Roper
Tyler Roper

Reputation: 21672

Generally speaking, when you first foray into SQL, it's best not to relate it to other common non-RDBMS languages. It's entirely different, such to the extent that if you ever find yourself iterating in SQL (using something like WHILE or CURSOR), it's almost always a red flag.

update certain rows, from range 96811 to 96815

All you need is...

UPDATE MyTable              -- Update your table
SET MyValue = MyValue + 1   -- Increase MyValue by 1
WHERE Id >= 96811           -- Wherever the ID is between 96811 and 96815
AND Id <= 96815

Upvotes: 1

Related Questions