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?
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? |