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


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to