On Tue, Dec 20, 2011 at 3:01 AM, Daniel Migowski <dmigow...@ikoffice.de> wrote: > DELETE FROM address > > WHERE id NOT IN (SELECT address_id FROM bank where address_id IS NOT NULL) > > AND id NOT IN (SELECT poboxaddress_id FROM bank where poboxaddress_id IS > NOT NULL) > > AND id NOT IN (SELECT address_id FROM bankconnection where address_id IS > NOT NULL)
You may find it faster if you rewrite it as an outer join. SELECT id FROM address LEFT JOIN bank ON address.id=bank.address_id LEFT JOIN pobox ON address.id=pobox.address_id LEFT JOIN bankconnection ON address.id=bankconnection.address_id WHERE bank.address_id IS NULL AND pobox.address_id IS NULL AND bankconnection.address_id IS NULL Something like that. (This will enumerate the IDs to be deleted. I always prefer to start with safe operations before moving to huge deletions!) Alternatively, use a multi-pass approach: CREATE TABLE deleteme (ID same_type_as_address) INSERT INTO deleteme SELECT id FROM address DELETE FROM deleteme WHERE id IN (SELECT address_id FROM bank) DELETE FROM deleteme WHERE id IN (SELECT address_id FROM pobox) DELETE FROM deleteme WHERE id IN (SELECT address_id FROM bankconnection) -- etc -- DELETE FROM address WHERE id IN (SELECT id FROM deleteme) Chris Angelico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
