Re: Feature Request: Better handling of foreign keys in DELETE statements

On Tue, Dec 20, 2011 at 3:01 AM, Daniel Migowski <dmigowski(at)ikoffice(dot)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