Hi



Thanks a lot for all the good and prompt responses. I'd say this is an unfortunate limitation and I hope something more convenient is implemented in the future.



We have been running embedded firebird systems for the last two years and never had significant problems with Firebird limitations and this one had decent workarounds so I am really happy that we choose to go the embedded firebird route. And as it seems, any and all limitations are well overshadowed by the good help that can be found in this active mailing list.



Thanks



Tim







________________________________

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Wednesday, September 12, 2007 2:36 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Delete with join and delete from multiple tables



At 09:03 PM 12/09/2007, you wrote:

>Hi
>
>I am running firebird 1.5 and cannot seem to figure out how to
>delete from one table based on data in another table.
>
>How can I implement:
>
>Delete from A where A.id in (select id from XXX, YYY where z=q)
>
>When the inline query runs in the hundreds of thousands?

Well, this is a very laborious approach compared to doing the delete
in an executable stored procedure in a loop....but, if you must:

Delete from A
where exists (
select 1 from XXX join YYY
on XXX.z = YYY.q
where XXX.id = A.id)

> From what I read and have seen in my tests this doesn't work if the
> inner select grows to more than a few hundred rows.

No - the limitation you are referring to is specifically the IN()
predicate where the predicates search values are a list of
constants. IN (subquery expression returning scalars), when
correctly presented, will resolve to the query above, so it is
worthwhile presenting it directly to save some processor cycles.

>In my case both the first and second table contains hundreds of
>thousands of rows.
>My real world select looks like the one below and works fins. And in
>this case I would actually like to delete all this data from all
>these four tables so I would really like to just substitute my
>select with a delete and run it.
>
>Select * from
> t_transactions tt,
> t_uploads tu,
> t_transaction_rows tr,
> t_transaction_row_content trc
>where
> tt.itransaction_id = tr.itransaction_id
> and tt.itransaction_id = trc.itransaction_id
> and tt.itransaction_id = tu.item_id
> and tu.status = 'INSYNC'
> and TU.Item_type='TRANS' and tt.ts < current_date - 60

Well, you can never delete rows from multiple tables in a single DSQL
DELETE statement. If you need to do that, you certainly need to
write an executable procedure for it.

>Anyway I am trying to make a clean up script for a number of
>databases so I would really like to know what is the "proper way" of
>deleting lots of data in one table depending on the contents in
>another table (with or without deleting the data in the other
>tables). I am managing hundreds of embedded databases so it is
>really not an option to use some trixy external tools. I need to use
>SQL scripts (or possibly if necessary a way to do it with a stored
>procedure but it certainly is a lesser option) that I can push out
>to our systems.

Suppose you want to target t_transactions for multiple deletes based
on the set above:

delete from t_transactions tt
where
tt.ts < current_date - 60)
and exists (
select 1 from t_transaction_rows tr
join t_transaction_row_content trc
on trc.itransaction_id = tr.itransaction_id
join t_uploads tu
on tu.item_id = trc_transaction_id
where
tr.itransaction_id = tt.itransaction_id
and tu.status = 'INSYNC'
and TU.Item_type='TRANS' )

./hb





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