Sorry, I realise I made a mistake in my schema and my query below, they
should read:
-----
UPDATE foo_table
SET NextDate=DATE_ADD(foo_table.NextDate,Period)
WHERE NextDate<=NOW()
-----
CREATE TABLE `foo_table` (
`ID` bigint(20) NOT NULL auto_increment,
`Period` enum('INTERVAL 30 SECOND','INTERVAL 1 DAY','INTERVAL 1
WEEK','INTERVAL 2 WEEK','INTERVAL 1 MONTH','INTERVAL 2 MONTH','INTERVAL 3
MONTH','INTERVAL 1 YEAR','INTERVAL 2 YEAR') NOT NULL default 'INTERVAL 1
MONTH',
`NextDate` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`ID`)
) TYPE=InnoDB;
-----
> I have also considered trying something along the lines of:
>
> UPDATE foo_table
> SET NextDate=DATE_ADD(foo_table.NextDate,(SELECT foo_table.Period))
> WHERE NextDate<=NOW()
-----
The more I look at this, the more I think I may be forced to select the date
interval to add and then build a query and execute it rather than do that in
just one statement. I would have preferred to have this work (or something
similar).
--
Adam Carmichael
[EMAIL PROTECTED]
----- Original Message -----
From: "Adam Carmichael" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, September 30, 2003 9:45 PM
Subject: DATE_ADD Dynamic Interval
> Hi All,
>
> I am trying to create a table with a list of records where a script runs
> about once every 10 minutes that will update a certain field by an
interval
> set (by an enum) in that particular record.
>
> My Script looks as follows:
>
> UPDATE foo_table
> SET NextDate=DATE_ADD(foo_table.NextDate,Period)
> WHERE NextDate<=NOW()
>
> And the schema approximates:
> CREATE TABLE `Records` (
> `ID` bigint(20) NOT NULL auto_increment,
> `Period` enum('INTERVAL 30 SECOND','INTERVAL 1 DAY','INTERVAL 1
> WEEK','INTERVAL 2 WEEK','INTERVAL 1 MONTH','INTERVAL 2 MONTH','INTERVAL 3
> MONTH','INTERVAL 1 YEAR','INTERVAL 2 YEAR') NOT NULL default 'INTERVAL 1
> MONTH',
> `NextDate` datetime NOT NULL default '0000-00-00 00:00:00',
> PRIMARY KEY (`ID`)
> ) TYPE=InnoDB;
>
> I have also considered trying something along the lines of:
>
> UPDATE foo_table
> SET NextDate=DATE_ADD(foo_table.NextDate,(SELECT foo_table.Period))
> WHERE NextDate<=NOW()
>
> Has anybody tried anything like this before with any luck?
>
> Regards,
>
> Adam Carmichael
> [EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
