Francisco Reyes wrote:
Lew writes:
Strange? Why? Did you expect a particular statistical distribution?
Perhaps
The impression was that one query was returning everything.. and the
other only the records that did not exist in the one table.
you were surprised by the extent of the situa
Lew writes:
Strange? Why? Did you expect a particular statistical distribution? Perhaps
The impression was that one query was returning everything.. and the other
only the records that did not exist in the one table.
you were surprised by the extent of the situation, not thinking there
Your top-posting was confusing to me, but I eventually figured out what went
where.
Francisco Reyes wrote:
However.. I find it very strange that just the selects by themselves
produced the same ouput up to limit 100.
Strange? Why? Did you expect a particular statistical distribution? Perha
Scott Marlowe writes:
Note that if you're in a transaction, you don't technically need the
backup (doesn't hurt though) as if you get it wrong you can just roll it
back.
I know.. I do the backup in case I forget to do the transaction. :-)
---(end of broadcast)---
Francisco Reyes wrote:
Alban Hertroys writes:
I suppose you run those queries in a transaction block, right?
Correct.
Also I do a backup before doing the deletions.
Note that if you're in a transaction, you don't technically need the
backup (doesn't hurt though) as if you get it wrong you
Alban Hertroys writes:
Why not use EXISTS?
DELETE FROM export_messages WHERE NOT EXISTS (
SELECT 1
FROM exports
WHERE exports.export_id = export_messages.export_id
)
Didn't think of it. Thanks for the code.
I suppose you run those queries in a transaction block, r
Francisco Reyes wrote:
> 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)
> );
Why not use EXISTS?
DELETE FROM export_messages
Joris Dobbelsteen writes:
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;
In my case I needed "IS NULL"
Y
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 you
>-Original Message-
>From: Francisco Reyes [mailto:[EMAIL PROTECTED]
>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:
>
>>
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.
Thought: are you sure you are going to delete those rows? In there cases
human verification is
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 t
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_message
13 matches
Mail list logo
