Re: Delete with subquery deleting all records - Mailing list pgsql-general

From Joris Dobbelsteen
Subject Re: Delete with subquery deleting all records
Date
Msg-id 73427AD314CC364C8DF0FFF9C4D693FF037B44@nehemiah.joris2k.local
Whole thread Raw
In response to Delete with subquery deleting all records  (Francisco Reyes <lists@stringsutils.com>)
List pgsql-general
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: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] 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


pgsql-general by date:

Previous
From: "Chuck D."
Date:
Subject: Geographic data sources, queries and questions
Next
From: "Harpreet Dhaliwal"
Date:
Subject: Re: Vacuum DB in Postgres Vs similar concept in other RDBMS