From: | "Joris Dobbelsteen" <Joris(at)familiedobbelsteen(dot)nl> |
---|---|
To: | "Francisco Reyes" <lists(at)stringsutils(dot)com> |
Cc: | "PostgreSQL general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Delete with subquery deleting all records |
Date: | 2007-05-23 23:30:15 |
Message-ID: | 73427AD314CC364C8DF0FFF9C4D693FF037B44@nehemiah.joris2k.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)postgresql(dot)org
>[mailto:pgsql-general-owner(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Fitzpatrick | 2007-05-23 23:48:10 | Re: Searching data across tables, some large |
Previous Message | Chuck D. | 2007-05-23 23:22:16 | Geographic data sources, queries and questions |