Re: [SQL] DELETE using an outer join
Tom Lane, 19.07.2012 16:52: If you're using a reasonably recent version of PG, replacing the NOT IN by a NOT EXISTS test should also help. Thanks. I wasn't aware of that (and the NOT EXISTS does indeed produce the same plan as the OUTER JOIN solution) Now I was wondering if a DELETE statement could be rewritten with the same strategy: Not at the moment. There have been discussions of allowing the same table name to be respecified in USING, but there are complications. Thanks as well. It's not a big deal for me. I was just curious if I missed 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
Re: [SQL] DELETE using an outer join
On Thu, Jul 19, 2012 at 6:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Now I was wondering if a DELETE statement could be rewritten with the same strategy: Not at the moment. There have been discussions of allowing the same table name to be respecified in USING, but there are complications. However it works. 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; -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE using an outer join
Sergey Konoplev, 20.07.2012 10:21: On Thu, Jul 19, 2012 at 6:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Now I was wondering if a DELETE statement could be rewritten with the same strategy: Not at the moment. There have been discussions of allowing the same table name to be respecified in USING, but there are complications. However it works. 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; But that's not an outer join -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE using an outer join
On Fri, Jul 20, 2012 at 2:27 PM, Thomas Kellerer spam_ea...@gmx.net wrote: Now I was wondering if a DELETE statement could be rewritten with the same strategy: Not at the moment. There have been discussions of allowing the same table name to be respecified in USING, but there are complications. However it works. 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; But that's not an outer join Oh, yes. I just lost the discussion line. Sorry. -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE using an outer join
Sergey Konoplev gray...@gmail.com writes: On Thu, Jul 19, 2012 at 6:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Now I was wondering if a DELETE statement could be rewritten with the same strategy: Not at the moment. There have been discussions of allowing the same table name to be respecified in USING, but there are complications. However it works. 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. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE using an outer join
On Fri, Jul 20, 2012 at 5:51 PM, Tom Lane t...@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...@gmail.com Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
