Hint: LEFT JOIN is your mistake...
Thought: are you sure you are going to delete those rows? In there cases
human verification is usually the way to go, though it takes a lot of
time.
Read on...
>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
>Francisco Reyes
>Sent: donderdag 24 mei 2007 1:12
>To: PostgreSQL general
>Subject: [GENERAL] Delete with subquery deleting all records
>
>I have two tables
>exports
>export_messages
>
>They were done without a foreign key and I am trying to clean
>the data to put a constraint.
>
>For every record in exports_messages there is supposed to be a
>matching record in exports with a export_id (ie export_id is
>the foreign key for
>export_messages)
>
>The following query identified all records that I need to delete:
>SELECT distinct export_messages.export_id as id FROM
>export_messages LEFT OUTER JOIN exports ON
>(export_messages.export_id = exports.export_id);
>
>I checked a number of them.. and all the records returned from
>that select do not have a matching export_id in exports.
>
>When I try to run:
>delete from export_messages where export_id in (SELECT
>distinct export_messages.export_id as id FROM export_messages
>LEFT OUTER JOIN exports ON (export_messages.export_id =
>exports.export_id) );
>
What seems more resonable:
DELETE FORM export_messages
WHERE NOT export_id IN (SELECT export_id FROM exports)
Make sure you know what you are doing (backup)...
[snip]
- Joris