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: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
