Re: DELETE using an outer join

On Thu, Jul 19, 2012 at 4:43 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
>    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)

If you want to remove duplicates you can do it this way.

DELETE FROM some_table USING some_table AS s
WHERE   some_table.col1 = s.col1 AND   some_table.col2 = s.col2 AND   some_table.id < s.id;

The query plan should be better than one with the sub query and NOT IN.

ps. May be this example is worth to append to the documentation?

-- 
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204