Thanks Helen

Great advice and examples. I look forward to receiving my copy of your book :)


Rob Martin
Software Engineer

phone 03 377 0495
fax 03 377 0496
web www.chreos.com

----- Original Message -----
From: Helen Borrie
To: IBObjects@yahoogroups.com
Sent: Thursday, August 12, 2004 7:05 PM
Subject: Re: [IBO] Advice


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



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !


Yahoo! Groups Sponsor
ADVERTISEMENT





------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/IBObjects/

b.. To unsubscribe from this group, send an email to:
IBObjects-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]