Re: Delete with subquery deleting all records

[Francisco Reyes <lists(at)stringsutils(dot)com>]

Joris Dobbelsteen writes:

> 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?

Correct.
I thought of that.. and the outerjoin as I showed.. only shows values that 
are in export_messages but are not in exports.

I went over nearly 100 values and that select only had the right values.  
 
> 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;

Thanks will try it.

> The LEFT OUTER join will at all times return ALL rows from
> export_messages.

What is very, very strange is that it didn't return all values.

> In effect, you generate a list with ALL export_messages.expor
> we must conclude that for every row you are trying to delete, the
> condition must evaluate to true.

This is what was driving me crazy.. when I did the select by itself the list 
was correct.

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

Thanks much. Will try your query.
doing all this within a transaction so I can double check the results.. that 
is the primary reason i would rather get it done from within psql.
If I do it in a program I will have no easy way to tell if I am doing the 
right thing... Small tests.. and print statements will helpfully help, but 
once I believe the program is working.. and run it.. the only solution is a 
restore (I do a backup before doing any changes of course).