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?

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. In my case both the first and second table contains hundreds of thousands of rows.

I can easily join them together in a select but I cannot seem to figure out how to delete from this select

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


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.


Any pointers appreciated.

Cheers

Tim