At 07:09 AM 9/01/2004 +0000, you wrote:

>OK I have been here several times before, and my solution
>has been to split the problem in a client application, but
>now that I am using PHP, that option is not practical.
>
>Version of Firebird does not seem to matter, but I do now
>have 1.5RC8 up.
>
>Table ACTIONS has 140000 records from historic data.
>ID = INTEGER PRIMARY KEY
>TICKET_REF = TIMESTAMP
>SERVED = INTEGER
>
>SELECT A.TICKET_REF FROM ACTIONS A
>GROUP BY A.TICKET_REF
>HAVING COUNT(A.TICKET_REF) = 7
>
>Gives a single result ( fast )
>= 6 down to = 2 gives an increasing number of results with
>12000 'pairs' of entries.
>
>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 )
>
>Is simple enough, hopefully you can see where I am going ( I
>want to run it for 6 down to 2 as well ;) ), and I'm not too
>concerned about the time it takes - except after 3 hours it
>had not finished!
>
>Now I can drop into Builder and get the result I want quite
>quickly, simply by dropping the results of the first query
>into a simple update. WHY can't I do that in one hit?

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?

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

or

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:

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

/heLen