Creating a loop in SQL with reference to previous iteration

Question

I have a dataset with columns:

What I need to do is to create a loop with such conditions:

For Index = 1

if Column1 >= 1 then Column2 = 1 and Column3 = Column1 - 1
if Column1 < 1 then Column 2 = 0 and Column3 = 0

For Index > 1

if Column1 + Column3 (from previous iteration!) >=1 then Column2 = 1 and Column3 = Column1 + Column3 (from previos iteration!) - 1
if Column1 + Column3 (from previous iteration) < 1 then Column2 = 0 and Column3 = 0

I need to update this column one row at the time, starting with Index = 1. I have tried to start with such WHILE loop:

WHILE (SELECT COUNT(*) FROM #TABLE WHERE Column2 IS NULL) > 0
UPDATE TOP (1) #TABLE
SET ...

And I simply can't achieve my desired outcome like this:

And of course I really, really want to avoid using Cursor. Thank you for your help!

I assume, Index column is Primary Key? Then Index = 1 will be only once right? — subham soni
– subham soni, Commented Jul 19, 2020 at 15:47
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using. Why should I tag my DBMS — user330315
– user330315, Commented Jul 19, 2020 at 16:40

David Browne - Microsoft · Accepted Answer · 2020-07-19 17:10:17Z

You can use a recursive CTE for this, something like:

with t as
(
  select * 
  from (values (1,4,null,null),
               (2,0,null,null),
               (3,0,null,null),
               (4,0,null,null),
               (5,2,null,null),
               (6,0,null,null)) v(Id,Column1,Column2,Column3)
), q as
(
  select Id,
         Column1,
         case when Column1 >=1 then 1 else 0 end Column2,
         case when Column1 >=1 then Column1-1 else 0 end Column3
  from t 
  where id = 1
  union all
  select t.id, 
         t.Column1, 
         case when t.Column1 + q.Column3 >= 1 then 1 else 0 end Column2,
         case when t.Column1 + q.Column3 >= 1 then t.Column1+q.Column3-1 else 0 end Column3
         
  from t
  join q
    on q.id = t.id-1
)
select *
from q

outputs

Id          Column1     Column2     Column3
----------- ----------- ----------- -----------
1           4           1           3
2           0           1           2
3           0           1           1
4           0           1           0
5           2           1           1
6           0           1           0

(6 rows affected)

Collectives™ on Stack Overflow

Creating a loop in SQL with reference to previous iteration

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related