Delete with subquery deleting all records

From: Francisco Reyes <lists(at)stringsutils(dot)com>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: Delete with subquery deleting all records
Date: 2007-05-23 23:12:06
Message-ID: cone.1179961926.368338.29470.5001@35st.simplicato.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)
);

All records from export_messages get deleted.
Any suggestions?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chuck D. 2007-05-23 23:22:16 Geographic data sources, queries and questions
Previous Message Tom Allison 2007-05-23 22:56:32 What does this error mean?