Re: DELETE using an outer join

On Fri, Jul 20, 2012 at 5:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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;
>
> No, that's a self-join, which isn't what the OP wanted.  You can make it
> work if you self-join on the primary key and then left join to the other
> table, but that's pretty klugy and inefficient.
>
> What was being discussed is allowing people to write directly
>
> DELETE FROM some_table USING some_table LEFT JOIN other_table ...
>
> where the respecification of the table in USING would be understood
> to mean the target table.  Right now this is an error case because
> of duplicate table aliases.

Yes, the OP has already pointed me to it. Thank you for your explanation.

-- 
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