From: | Sergey Konoplev <sergey(dot)konoplev(at)postgresql-consulting(dot)com> |
---|---|
To: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: DELETE using an outer join |
Date: | 2012-07-19 14:33:45 |
Message-ID: | CAL_0b1tUmyEb7-Q1BCTCRjrd1GCXZe9pdRKteo6pppTk1URspw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thu, Jul 19, 2012 at 4:43 PM, Thomas Kellerer <spam_eater(at)gmx(dot)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(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-07-19 14:52:34 | Re: DELETE using an outer join |
Previous Message | Thomas Kellerer | 2012-07-19 12:43:46 | DELETE using an outer join |