Stored Procedure To Update smalldatetime Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure To Update smalldatetime Field

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-21 : 07:54:30

Timothy T. Maiden writes "I am trying to update fields in my table based on certain critera.

UPDATE tblALMLoans

SET tblALMLoans.NextRepDate = dateadd(YY,5,tblALMLoans.OriginalDate)

do until tblALMLoans.NextRepDate > getdate()
tblALMLoans.NextRepDate = dateadd(YY,1,tblALMLoans.OriginalDate)
loop

FROM tblALMLoans
WHERE (tblALMLoans.RateFlag = 'A');

I know this is no where near what its supposed to look like, but this is in code what I am looking to do.

Any help would be greatly appriciated."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-21 : 08:05:46

I think this is what you're going for:


update tblALMLoans set
      NextRepDate = dateadd(year,5,originalDate)
from   tblALMLoans
where  nextRepDate < getdate()

Sql Server is built to deal with "sets" of data at a time rather than a procedural, one-row-at-a-time approach. Your first update statement would have updated the entire table. You just needed to add the where clause.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources