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?
