Jeff,

the syntax of your statement is not correct. In addition: when you sort
the query in a descending order, you have to iterate from the first to
the last record. Otherwise you'll run into the same problem of duplicate
errors.

This should work:

SCRIPT
BEGIN
  DECLARE Readings SENSITIVE CURSOR FOR stmt;
  DECLARE FReadingDate DATE;
  PREPARE stmt FROM 'SELECT * FROM MeterReadings ORDER BY ReadingDate
DESC';
  OPEN Readings;
  FETCH FIRST FROM Readings('ReadingDate') into FReadingDate;
  WHILE NOT EOF(Readings) DO
    UPDATE Readings set 'ReadingDate' = FReadingDate + INTERVAL '1' MONTH;
    FETCH NEXT FROM Readings('ReadingDate') into FReadingDate;
  END WHILE;
END

Uli

Addition: probably you'll need a separate index on "ReadingDate" to get
a sensitive result, but that's a different thing.

On 8/03/2015 9:31 p.m., Uli Becker wrote:
> Addition: probably you'll need a separate index on "ReadingDate" to get
> a sensitive result, but that's a different thing.
>

Thanks to Roy, Charles, Roy and Mario

Got it now - at least in theory.  I'll put it to the test with corrected
syntax and the right sequence after I have done my jury duty this week.
Crossfingers there isn't a long trial tomorrow!

Cheers

jeff

On 8/03/2015 9:27 p.m., Roy Lambert wrote:
> Jeff
>
>
> Two questions:
>
> What do you want
>
> MeterID = 123 ReadingDate = 2015-01-01 ... etc
> MeterID = 123 ReadingDate = 2015-02-01 ... etc
>
>
> to become? Dependent on your answer I think I can come up with the necessary SQL.
>
 one month on ...

     MeterID = 123 ReadingDate = 2015-02-01 ... etc
     MeterID = 123 ReadingDate = 2015-03-01 ... etc
>
> Where can I download the demo data so I can test my "brilliance"?
>

Not downloadable at this stage.  Your brilliance would stand the test
regardless, I'm sure

Our current version which is DBISAM3 doesn't have the MeterReadings
table and our ElevateaDB version is a work in progress.

If interested, look at Uli's response - rotten syntax and a silly logic
error are the problem.

Cheers and thanks for you help as ever

Jeff

On 8/03/2015 9:55 p.m., Jeff Cook wrote:
> On 8/03/2015 9:31 p.m., Uli Becker wrote:
>> Addition: probably you'll need a separate index on "ReadingDate" to get
>> a sensitive result, but that's a different thing.
>>
>
> Thanks to Roy, Charles, Roy and Mario
>
> Got it now - at least in theory.  I'll put it to the test with corrected
> syntax and the right sequence after I have done my jury duty this week.
> Crossfingers there isn't a long trial tomorrow!
>

Not wanted for a jury today so back at work.

Thanks Uli

Go it going now.  Had to change the ORDER BY to match the existing index
and it all works OK.

Many thanks to everyone who helped.  Final version is below

Cheers

Jeff
--
SCRIPT
BEGIN
  DECLARE Readings SENSITIVE CURSOR FOR stmt;
  DECLARE FReadingDate DATE;
  PREPARE stmt FROM 'SELECT * FROM MeterReadings ORDER BY MeterID,
ReadingDate';
  OPEN Readings;
  FETCH LAST FROM Readings('ReadingDate') into FReadingDate;
  WHILE NOT BOF(Readings) DO
    UPDATE Readings SET 'ReadingDate' = FReadingDate + INTERVAL '1' MONTH;
    FETCH PRIOR FROM Readings('ReadingDate') into FReadingDate;
  END WHILE;
END