Re: Delete with subquery deleting all records

["Joris Dobbelsteen" <Joris(at)familiedobbelsteen(dot)nl>]

>-----Original Message-----
>From: Francisco Reyes [mailto:lists(at)stringsutils(dot)com] 
>Sent: donderdag 24 mei 2007 2:04
>To: Joris Dobbelsteen
>Cc: PostgreSQL general
>Subject: Re: [GENERAL] Delete with subquery deleting all records
>
>Joris Dobbelsteen writes:
>
>> Hint: LEFT JOIN is your mistake...
>
>The use of left join in general.. or my left join?
>When I do the left join by itself I verified manually and it 
>had the data I wanted.

Your usage in this context...

Did you really check your list thoroughly.
>>>SELECT distinct export_messages.export_id as id
>>>FROM export_messages 
>>>>LEFT OUTER JOIN exports ON 
>>>(export_messages.export_id = exports.export_id);

Take any value from "SELECT export_id FROM exports"
Does it not exist in your list?

Try this:
SELECT distinct export_messages.export_id as id,
  exports.export_id as exports_export_id
FROM export_messages 
LEFT OUTER JOIN exports ON 
(export_messages.export_id = exports.export_id)
WHERE exports.export_id IS NOT NULL;

At this point you should know whats going wrong...

>>>DELETE FROM export_messages
>>>WHERE export_id IN
>>>   (SELECT distinct export_messages.export_id as id
>>>    FROM export_messages
>>>    LEFT OUTER JOIN exports

The LEFT OUTER join will at all times return ALL rows from
export_messages.
In effect, you generate a list with ALL export_messages.export_id. Thus
we must conclude that for every row you are trying to delete, the
condition must evaluate to true.

>>>    ON (export_messages.export_id = exports.export_id)
>>>    );

>> 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.
>
>If I can't not find a way to do this through SQL I will write 
>a program.
>The time to try and clean this by hand would be countless 
>hours. There are a lot of records in the child table that do 
>not have a matching record in the parent table.

That's the trade-off: effects of a mistake * chance of a mistake against
the cost to prevent these.

Hope this helps...

- Joris