At 03:25 PM 12/08/2004 +1200, you wrote:

>Hi All
>
>I want to replace some OLD BDE / DBase code like this... (rough example
>
>with Table
> First;
> while (Eof = False) do begin
> if (Source.Locate('aRef', FieldByName('ARef').AsInteger, [])
> = True) then begin
> Delete;
> end
> else begin
> Next;
> end;
> end;
>end;
>
>I had thought of something like this
>
>AnUpdateQuery.SQL.Text = 'DELETE FROM Table WHERE aRef IN (SELECT ARef
>FROM Source)';
>AnUpdateQuery.ExecSQL
>
>
>However I suspect the performance of the above SQL will be horrible. You
>can't do JOINs in Delete statements so what would be the most appropriate
>/ best performance method of achieving this result?

DELETE FROM Table t
WHERE EXISTS(
SELECT 1 FROM Source s
where s.aRef = t.aRef)

Firebird should automatically optimise your query to this, anyway.

A faster solution is to write a SP to do it. It has the advantage of using
an appropriate index, if available, and it also avoids any "walking"
through subqueries.

CREATE PROCEDURE DEL_FROM_T1
AS
DECLARE InKey integer; /* or whatever type aRef is */
BEGIN
FOR SELECT t.aRef from Table t
join Source s on s.aRef = t.aRef
into :InKey do
DELETE FROM Table t1
where t1.aRef = :InKey;
END

HB