DELETE using an outer join

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: DELETE using an outer join
Date: 2012-07-19 12:43:46
Message-ID: ju8veb$dkn$1@dough.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Sergey Konoplev 2012-07-19 14:33:45 Re: DELETE using an outer join
Previous Message Puneet Mishra 2012-07-18 06:48:13 Order preservation of search phrases in postgresql FTS/OpenFTS