--- In firebird-support@yahoogroups.com, "tickerboo2002" <egroup@...>
wrote:

>
> Hello
>
> Inside a Trigger, I'd like to perform an update if a select returns no
> rows. Something like the following:
>
> UPDATE foo f
> SET f.Finished=1
> where f.ID=100 and count( select bar_Id from bar where blah )=0

That is a really bad way to do it (even if the syntax was good). You
are telling Firebird that it must count EVERY record in bar, where in
fact you don't care if there is 3 or 100 million. You are waiting for
a long time unnecessarily.

UPDATE FOO F
SET F.FINISHED = 1
WHERE F.ID = 100
AND NOT EXISTS
(
SELECT B.BAR_ID
FROM BAR B
WHERE B.BLAH = ..... <-- your original query does not say enough
)

Note that you should really use aliases everywhere, otherwise you will
have problems when ambiguous fields exist.

If you then ran that query, it would use the (I assume) index on ID
(sounds like a PK constraint). Once it had that record, it would
perform the subquery but it would stop once it had found just a single
record.

Although not exists checks are expensive if in fact nothing does
exist, providing you have an index on blah it should be acceptable.

Adam