Setting up a loop in SQL

Question

Can someone tell me how to do this as a loop in SQL?

update Bank set Report_date_next='2014-02-28' where Reporting_date='2014-01-31'
update Bank set Report_date_next='2014-03-31' where Reporting_date='2014-02-28'
update Bank set Report_date_next='2014-04-30' where Reporting_date='2014-03-31'
update Bank set Report_date_next='2014-05-31' where Reporting_date='2014-04-30'
update Bank set Report_date_next='2014-06-30' where Reporting_date='2014-05-31'
update Bank set Report_date_next='2014-07-31' where Reporting_date='2014-06-30'

The list goes on for quite a while so I need a counter on how to find the last cell as well similar to the XlDown in VBA.

Possible duplicate of Loop in stored procedure in SQL server — Daniel B
– Daniel B, Commented Dec 7, 2017 at 19:46

Aaron Dietz · Accepted Answer · 2017-12-08 17:03:30Z

2

You're way better off using a CASE statement:

UPDATE Bank
SET Report_date_next = CASE WHEN Reporting_date = '2014-01-31' THEN '2014-02-28'
                            WHEN Reporting_date = '2014-02-28' THEN '2014-03-31'
                            WHEN ...etc
                        END

If you tag your question with the DBMS you're using, there may be an even easier way depending on the functions available to you.

For instance, SQL Server 2012+ you can do:

UPDATE Bank
SET report_date_next = EOMONTH(Reporting_date,1)

edited Dec 8, 2017 at 17:03

answered Dec 7, 2017 at 19:48

Aaron Dietz

10.3k1 gold badge17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Dietz Over a year ago

@SergeKashlik NP, I was able to simplify it a bit farther too. The DATEADD was unnecessary

Collectives™ on Stack Overflow

Setting up a loop in SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related