CODEWITHSUNDEEP
phpmysqlsqlsql-update
shablm
shablm

Reputation: 25

Mysql update query, setwise, incrementing date

I have to update a column (called 'next-interaction') in a table. The column contains date-time as values (ex: '2011-07-12 18:30:00' ). I have 160 records after applying all my where clauses.

Now, I have to make sets of 15 records and update their next-interaction column with dates starting from '2014-01-06 16:00:00' (excluding weekend dates).

In the sense, 

    first 15 records -> 2014-01-06 16:00:00  ( -> date-interaction column )
    next 15 records -> 2014-01-07 16:00:00
    next 15 records -> 2014-01-08 16:00:00

like wise, excluding saturdays and sundays dates to be incremented.

How can I do this? Can I do this with queries alone, or should i have to write a small php-script for this?

Upvotes: 0

Views: 219

Answers (1)

Thomas
Thomas

Reputation: 618

I would use a Cursor to solve the problem. A Cursor allows you to go through all the records of a table sequentially.

  • Declare and open a Cursor (link)
    • something like DECLARE cur1 CURSOR FOR SELECT next-interaction FROM your_table;
  • Declare a "counter variable" i.e. "i" and a "date variable" i.e. "d"
  • As long as the counter variable < 15 -> update the record
  • If the counter variable >= 15, set it to 0 and increment your date variable by 1 day, something like d = DATE_ADD(d ,INTERVAL 1 DAY) and update the record
  • With DAYOFWEEK(d) = 6 or DAYOFWEEK(d) = 7 you can check, if your date is a weekend

If you have to execute the code regularly I would recommend declaring a stored procedure (link)

I haven't tested this.

Upvotes: 1

Related Questions