Den 2013-03-20 01:06 skrev Helen Borrie såhär:

>
> At 02:08 a.m. 20/03/2013, rosssmith55 wrote:
> >Hello! I am fairly new to learning SQL, I am attempting to learn how
> to do a Join > Delete using flamerobin and was told this is a great
> place to learn good stuff :) Any way this is what i got so far.
> >
> >SELECT a.INV_PARTNUMBER, a.INV_ALTPARTNUMBER, b.INVSUP_SUPPNUM,
> a.INV_DESCRIPTION1, a.INV_DESCRIPTION2, a.INV_LOCATIONID, a.INV_UOMID,
> a.INV_DEPTID, a.INV_STOCKONHAND, a.INV_COUNTEDDATE
> >FROM INVENTORY a
> >join INVENTORY_SUPPLIER b
> >on a.INV_ID = b.INVSUP_INVID
> >where a.INV_COUNTEDDATE < '01.10.2012, 00:00:00.001'
> >and a.inv_locationid = -1
> >and b.INVSUP_SUPPNUM = 10
> >
> >
> >As you can see I have joined a few things (in this instance I will be
> marking inventory items as deleted) I just cant for the life of me
> figure out how to do it! I know how to just simply delete something
> but not in a join and for this it must be done in a join.
>
> In SQL you can only delete rows from one table. In order to select
> which rows to delete, you provide a WHERE clause.
>

In my case I've had a somewhat similar case, needing to delete a few
thousand records from a table with roughly 170 million records, using
conditions involving a related table with almost as many records. Since
FB almost always resorts to natural scan for exists queries, a direct
delete query like Helen suggested was not an option.

instead I resorted to execute block, like this (in general terms, not
adapted to your specific case):

set term #;
execute block
as
declare Id bigint;
begin
for select I."Id"
from "Item" I
inner join "Related" R on R."Id" = I."RelatedId"
where I."Something" = 'Whatever'
and R."Name" = 'SomeName'
into :Id
do
begin
delete from "ItemChildren" where "ParentId" = :Id;
delete from "Item" where "Id" = :Id;
end
end#
set term ;#

This deletes the selected records including child records in a child
table, using a non-trivial select with join. This enables FB to use an
index and gave me good performance.

Regards,
Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post:kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64