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: Wed, 23 May 2007 19:12:06 -0400
- Message-id: <cone(dot)1179961926(dot)368338(dot)29470(dot)5001(at)35st(dot)simplicato(dot)com>
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?
Home |
Main Index |
Thread Index