>Now I want to delete all cars that *has* a record in "drivers", but the
>driver_name is null
>
>so far, I always do
>
>delete from cars
>where
>(
> select drivers.driver_name
> from drivers
> where
> cars.id_drivers = drivers.id
> )
> is null
>
>that works fine, but I just wander if there is a better way... just for
>curiosity...
What is the "best" way depends on several factors, including whether cars or
"null" drivers has the higest number of records and which Firebird version you
use. I would expect that in many cases, the most efficient query would be
similar to
EXECUTE BLOCK as
declare i int;
begin
for select d.id
from drivers d
where d.driver_name is null
into :i
do
begin
delete from cars c
where c.id_drivers = :i;
end
end
(I rarely use EXECUTE BLOCK, so there are probably some spelling mistakes)
Execute block only works with Firebird 2+, the more traditional way to write
your delete would either be to use a stored procedure or:
delete from cars c
where
exists(select *
from drivers d
where c.id_drivers = d.id
and d.driver_name is null)
HTH,
Set
