At 08:15 AM 9/01/2004 +0000, you wrote:

>Helen Borrie wrote:
>
> >>Version of Firebird does not seem to matter, but I do now
> >>have 1.5RC8 up.
>
>OK THAT was a bad move ...

Errm, yes...actually reminds me of a puzzle hereabouts a couple of years
ago. The problem is, your operation is inherently recursive because your
metadata doesn't provide you with any way to know whether the value has
already been processed. It just gets into a tighter and tighter spiral,
heading towards infinity in ever-decreasing nibbles until it finally
expires from hyperventilation.

I doubt you will do it in one DSQL statement, nor without a metadata change
- add a flag.

by update in a FOR loop
for...blah..
do
update...
set served = served/7,
flag = 1
where ticket_ref = :ticket_ref;

end
then finally (after the loop)
update...
set flag = 0
where flag = 1;

>The '7' select only returns one TICKET_REF value with 7
>elements. If I manually enter that value in place of the
>SELECT it finishes in seconds.
>
>What I am trying to do is change the serving time on each of
>those seven entries to be 1/7th of the current entry. The
>current SERVED time is the total across all the actions
>making up that activity where as the SUM() should equal that
>value ( forget rounding problems - I just don't want 10 mins
>so become 70 mins, 7 mins is good enough :) )
>
>Then I want to do 6,5,4,3 and 2 - which has 12000 entries.
>
> > I think a correlated EXISTS() predicate would supply the right logic:
> >
> > UPDATE ACTIONS U
> > SET U.SERVED = U.SERVED / 7
> > WHERE exists(
> > SELECT A.TICKET_REF FROM ACTIONS A
> > where a.ticket_ref = U.TICKET_REF
> > GROUP BY A.TICKET_REF
> > HAVING COUNT(A.TICKET_REF) = 7 ))
>
>( Removed the last ')' )
>THAT has just crashed RC8 as well!
>But I don't see it giving me the right answer anyway - it is
>NOT updating all 7 entries?

Doesn't make sense. As long as the loop gives it the list of ticket_refs,
it ought to find all of them matching the one it hits in the loop. See
what happens when you anchor the updated row with that flag.

If you get that bit fixed, you should be able to add an extra outer WHILE
loop to decrement the divisor for the calc, and do all of the groupings in
one proc.

>Perhaps this is where I am going wrong. I have a rule of not
>changing metadata on site. Perhaps I have to modify that
>rule, but I can't see why processing this as simple SQL is a
>problem?

Well, "simple SQL" it's not. But a single (if slow) DSQL statement for
each number might work if you didn't need to perform the same update of the
flag more than once in the life of a record. That flag would have to be
reset in a following statement inside the same transaction, otherwise.

/h