Tags: actual, exampleif, job, loop, programming, repeat, run, sql, statement, update

repeat a statement in a loop

On Programmer » SQL

8,118 words with 5 Comments; publish: Thu, 29 May 2008 02:08:00 GMT; (20042.97, « »)

hello,

i need to repeat the update statement for all days in the actual month

example:if i run today (19.09.2005) the job - it must run 19 times for

the value of

GETDATE() to GetDate() -19. How can be done this in a loop.

In the update statement is GetDate() used in the where condition and it must

be replaced with the values

in the first loop with GetDate() -1

in the second loop with GetDate() - 2

...

in the last GetDate()-19

The update statement looks like :

UPDATE [table1]

SET [OrderDate]=(

SELECT DISTINCT Top 1 OrderCreationDate

FROM table2

WHERE (SalesDocNr = table1.OrderNr)

)

WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (

SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d

FROM table1 INNER JOIN

table2 ON table1.OrderNr = table2.SalesDocNr

WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) = CONVERT(VarChar(10), GETDATE() , 104))

)

thanks

Xavier

All Comments

Leave a comment...

  • 5 Comments
    • While can use a WHILE loop or a cursor to get this done as you want, a

      better approach is to use a single UPDATE statement which can update all the

      rows.

      Based on the sample code you posted, it is hard to work out such a solution,

      so please refer to www.aspfaq.com/5006 and post relevant information for

      others to reproduce your problem.

      --

      Anith

      #1; Thu, 29 May 2008 02:10:00 GMT
    • For a looped approach try this for your basic loop. .sql.todaysummary.com.date should be used in

      place of GETDATE() in your update query. Replace the PRINT statement with

      your UPDATE query. I did a quick conversion of GETDATE() to get the DATE

      ONLY. You may want a better method than what I did.

      Mike

      DECLARE .sql.todaysummary.com.day int

      DECLARE .sql.todaysummary.com.date datetime

      SET .sql.todaysummary.com.day = DAY(GETDATE())

      SET .sql.todaysummary.com.date = GETDATE()

      SET .sql.todaysummary.com.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)

      WHILE .sql.todaysummary.com.day > 0

      BEGIN

      PRINT .sql.todaysummary.com.date

      SET .sql.todaysummary.com.day = .sql.todaysummary.com.day - 1

      SET .sql.todaysummary.com.date = DATEADD(d, -1, .sql.todaysummary.com.date)

      END

      "Xavier" <Xavier.sql.todaysummary.com.discussions.microsoft.com> wrote in message

      news:557E3B53-C67C-40BF-A62A-7674E0659CD3.sql.todaysummary.com.microsoft.com...

      > hello,

      > i need to repeat the update statement for all days in the actual month

      > example:if i run today (19.09.2005) the job - it must run 19 times for

      > the value of

      > GETDATE() to GetDate() -19. How can be done this in a loop.

      > In the update statement is GetDate() used in the where condition and it

      > must

      > be replaced with the values

      > in the first loop with GetDate() -1

      > in the second loop with GetDate() - 2

      > ...

      > in the last GetDate()-19

      > The update statement looks like :

      > UPDATE [table1]

      > SET [OrderDate]=(

      > SELECT DISTINCT Top 1 OrderCreationDate

      > FROM table2

      > WHERE (SalesDocNr = table1.OrderNr)

      > )

      > WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (

      >

      > SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d

      > FROM table1 INNER JOIN

      > table2 ON table1.OrderNr = table2.SalesDocNr

      > WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) => CONVERT(VarChar(10), GETDATE() , 104))

      > )

      > thanks

      > Xavier

      #2; Thu, 29 May 2008 02:11:00 GMT
    • Please post DDL (CREATE TABLE), sample data (INSERTs) and show your required

      results.

      I'm certain it's possible to do what you want in a single UPDATE statement

      without a loop. However, the UPDATE you have posted may not give reliable

      results because you've used TOP without ORDER BY. For that reason it's tricky

      to guess what you intended by it (even though it may not always work as you

      wanted).

      I expect the solution will look like

      :

      UPDATE Table1

      SET orderdate = (

      ...

      )

      WHERE insertdate >= DATEADD(DAY,-19,CURRENT_TIMESTAMP)

      AND insertdate <= CURRENT_TIMESTAMP ;

      --

      David Portas

      SQL Server MVP

      --

      "Xavier" wrote:

      > hello,

      > i need to repeat the update statement for all days in the actual month

      > example:if i run today (19.09.2005) the job - it must run 19 times for

      > the value of

      > GETDATE() to GetDate() -19. How can be done this in a loop.

      > In the update statement is GetDate() used in the where condition and it must

      > be replaced with the values

      > in the first loop with GetDate() -1

      > in the second loop with GetDate() - 2

      > ...

      > in the last GetDate()-19

      > The update statement looks like :

      > UPDATE [table1]

      > SET [OrderDate]=(

      > SELECT DISTINCT Top 1 OrderCreationDate

      > FROM table2

      > WHERE (SalesDocNr = table1.OrderNr)

      > )

      > WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (

      >

      > SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d

      > FROM table1 INNER JOIN

      > table2 ON table1.OrderNr = table2.SalesDocNr

      > WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) => CONVERT(VarChar(10), GETDATE() , 104))

      > )

      > thanks

      > Xavier

      #3; Thu, 29 May 2008 02:12:00 GMT
    • it works,

      thanks Mike

      "Mike Jansen" wrote:

      > For a looped approach try this for your basic loop. .sql.todaysummary.com.date should be used in

      > place of GETDATE() in your update query. Replace the PRINT statement with

      > your UPDATE query. I did a quick conversion of GETDATE() to get the DATE

      > ONLY. You may want a better method than what I did.

      > Mike

      >

      > DECLARE .sql.todaysummary.com.day int

      > DECLARE .sql.todaysummary.com.date datetime

      >

      > SET .sql.todaysummary.com.day = DAY(GETDATE())

      > SET .sql.todaysummary.com.date = GETDATE()

      > SET .sql.todaysummary.com.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)

      > WHILE .sql.todaysummary.com.day > 0

      > BEGIN

      > PRINT .sql.todaysummary.com.date

      > SET .sql.todaysummary.com.day = .sql.todaysummary.com.day - 1

      > SET .sql.todaysummary.com.date = DATEADD(d, -1, .sql.todaysummary.com.date)

      > END

      >

      > "Xavier" <Xavier.sql.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:557E3B53-C67C-40BF-A62A-7674E0659CD3.sql.todaysummary.com.microsoft.com...

      > > hello,

      > >

      > > i need to repeat the update statement for all days in the actual month

      > > example:if i run today (19.09.2005) the job - it must run 19 times for

      > > the value of

      > > GETDATE() to GetDate() -19. How can be done this in a loop.

      > >

      > > In the update statement is GetDate() used in the where condition and it

      > > must

      > > be replaced with the values

      > > in the first loop with GetDate() -1

      > > in the second loop with GetDate() - 2

      > > ...

      > > in the last GetDate()-19

      > >

      > > The update statement looks like :

      > >

      > > UPDATE [table1]

      > > SET [OrderDate]=(

      > >

      > > SELECT DISTINCT Top 1 OrderCreationDate

      > > FROM table2

      > > WHERE (SalesDocNr = table1.OrderNr)

      > >

      > > )

      > >

      > > WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (

      > >

      > >

      > > SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d

      > > FROM table1 INNER JOIN

      > > table2 ON table1.OrderNr = table2.SalesDocNr

      > > WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) => > CONVERT(VarChar(10), GETDATE() , 104))

      > >

      > > )

      > >

      > > thanks

      > > Xavier

      >

      >

      #4; Thu, 29 May 2008 02:13:00 GMT
    • > While can use a WHILE loop or a cursor to get this done as you want, a

      > better approach is to use a single UPDATE statement which can update all

      > the rows.

      I don't think that's really possible without SQL Server 2005 (or perhaps a

      _really_ messed up query in SQL 2000), in which case it would be something

      like this as a base:

      WITH DAYS(DayValue, Remaining) AS

      (

      SELECT

      CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime) AS DayValue,

      DAY(GETDATE()) - 1

      UNION ALL

      SELECT

      DATEADD(d, -1, r.DayValue) AS DayValue,

      r.Remaining - 1

      FROM

      DAYS r

      WHERE

      r.Remaining > 0

      )

      SELECT * FROM DAYS;

      Instead of SELECT * FROM DAYS you'd do an UPDATE and JOIN to DAYS.

      Mike

      #5; Thu, 29 May 2008 02:14:00 GMT