Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

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

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group