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 ...

<Snip>

>>UPDATE ACTIONS U
>>SET U.SERVED = U.SERVED / 7
>>WHERE U.TICKET_REF IN
>>( SELECT A.TICKET_REF FROM ACTIONS A
>>GROUP BY A.TICKET_REF
>>HAVING COUNT(A.TICKET_REF) = 7 )

Actually CLOSES DOWN the computer after 10mins or so, rather
than just running and not finishing!

> This logic seems wrong (or I've been have too many cherry cocktails) - it's
> not correlated, so it's roiling away there updating every row with every
> value it derives in the the IN set, isn't it? Would it *ever* finish?

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?

> WHERE exists(
> SELECT A.TICKET_REF FROM ACTIONS A
> GROUP BY A.TICKET_REF
> HAVING COUNT(A.TICKET_REF) = 7
> and a.ticket_ref = U.TICKET_REF)
>
> ....but wouldn't be as fast as a SP, which doesn't have to correlate anything:

Except that the sort of enquiry I am trying to cope with
will have to be input via a PHP query ...

> DECLARE VARIABLE VTICKET_REF SOME_TYPE;
> BEGIN
> FOR SELECT A.TICKET_REF FROM ACTIONS A
> GROUP BY A.TICKET_REF
> HAVING COUNT(A.TICKET_REF) = 7
> INTO :VTICKET_REF DO
> UPDATE ACTIONS
> SET SERVED = SERVED/7
> WHERE TICKET_REF = :VTICKET_REF
> AND SERVED IS NOT NULL;
> END

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?

I'm going to drop back to RC6 and check again ...

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services