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