From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: DELETE using an outer join
Date: Thu, 19 Jul 2012 14:43:46 +0200
Message-ID: <ju8veb$dkn$1@dough.gmane.org> (raw)
Hi,
(this is not a real world problem, just something I'm playing around with).
Lately I had some queries of the form:
select t.*
from some_table t
where t.id not in (select some_id from some_other_table);
I could improve the performance of them drastically by changing the NOT NULL into an outer join:
select t.*
from some_table t
left join some_other_table ot on ot.id = t.id
where ot.id is null;
Now I was wondering if a DELETE statement could be rewritten with the same "strategy":
Something like:
delete from some_table
where id not in (select min(id)
from some_table
group by col1, col2
having count(*) > 1);
(It's the usual - at least for me - "get rid of duplicates" statement)
The DELETE .. USING seems to only allow inner joins because it requires the join to be done in the WHERE clause.
So I can't think of a way to turn that NOT IN from the DELETE into an outer join with a derived table.
Am I right that this kind of transformation is not possible or am I missing something?
Regards
Thomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: DELETE using an outer join
In-Reply-To: <ju8veb$dkn$1@dough.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox