I've been staring at this problem for some time, but can't seem to
make any progress. I'm using FB2. I have this select:

Select S1."Acc ID", S1."Serv ID", S1."Last Performed", X.LP FROM
sched s1
inner join
SCHED S on S."Acc ID" = S1."Acc ID"
and S."Serv ID" = S1."Serv ID"
inner join
(select A."Acc ID" as ACCID, A."Serv ID" as SERVID,
max(A."Date") as LP
from artrans a
where A."Code" in (0, 1, 2, 10)
group by A."Acc ID", A."Serv ID") X
on S."Acc ID" = X.ACCID and S."Serv ID" = X.SERVID
where S."Last Performed" <> X.LP

What I really would like to do is this:

update SCHED Set "Last Performed" = (X.LP from the select above)

I tried this:

update sched s1 set s1."Last Performed" = (select X.LP
from SCHED S
inner join
(select A."Acc ID" as ACCID, A."Serv ID" as SERVID,
max(A."Date") as LP
from artrans a
where A."Code" in (0, 1, 2, 10)
group by A."Acc ID", A."Serv ID") X
on S."Acc ID" = X.ACCID and S."Serv ID" = X.SERVID
where S."Last Performed" <> X.LP
and S."Acc ID" = S1."Acc ID"
and S."Serv ID" = S1."Serv ID" )

But it doesn't seem to do what I want because it updates all of the
rows, in apparent violation of the condition S."Last Performed" <>
X.LP which is, at this instant, always false, so I'd expect it to
update 0 rows until I change something.

In addition, while both SCHED and ARTRANS are smallish (5000 rows
and 60,000 rows respectively), the original SELECT takes a second to
run, so I'm thinking it's going to take 60,000 seconds to do this
update even if it was working, which is clearly too long.

In that regard, ARTRANS has an index on "Acc ID" (the first part of
the PK), but none on "Serv ID". The plan is this:

PLAN JOIN (SORT (X A NATURAL), S INDEX (PK_SCHED))
PLAN (S1 NATURAL)

So I guess PK_ARTRANS isn't useful here.


Any advice?

Michael D. Spence
Mockingbird Data Systems, Inc.