Delete from Join

--- On Wed, 7/2/08, Gwyneth Morrison <postgres (AT) toadware (DOT) ca> wrote:



> From: Gwyneth Morrison <postgres (AT) toadware (DOT) ca> > Subject: [GENERAL] Delete from Join > To: pgsql-general (AT) postgresql (DOT) org > Date: Wednesday, July 2, 2008, 3:15 PM > Hello, > > Is it possible to use a join keyword in a delete? > > For example: > > DELETE FROM data_table1 > using data_table2 INNER JOIN > data_table1 ON data_table1.fkey = > data_table2.pkey; > > > It is not directly mentioned in the delete syntax but the > delete refers > to the select clause where JOIN is valid. > > G >



>i have a example >delete from t1 a using t2 b where a.id = b.oid >A standard way to do it is >delete from t1 a where id in (select a.id from t1 a inner join t2 b on (a.id = b.oid)) Thank you for your reply, &nbsp;You are absolutely correct, it IS the standard way. What I am actually trying to do here is write a program to convert MS SQL to Postgres. I have had quite a bit of success so far, but this is a sticking point. Apparently using the JOIN keyword directly in a delete statement is valid in MS. I am trying to determine if it is valid in postgres which I figure it is not but cannot find it exactly in the documentation. So I guess the real question is, can the JOIN keyword be used directly in a delete as above. G

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

Tom Lane wrote:

Gwyneth Morrison <gwynethm (AT) toadware (DOT) ca> writes:



What I am actually trying to get past is:







DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey;



The equivalent to that in Postgres would be DELETE FROM data_table1 USING data_table2 WHERE data_table1.fkey = data_table2.pkey; The fundamental issue here is that MSSQL expects the USING clause to contain a second reference to the delete target table, whereas PG does not --- if you write the table name again, that's effectively a self-join and you probably won't get the behavior you want. You can use JOIN syntax in USING in Postgres, but only for situations where the query really involves three or more tables. regards, tom lane

Thank you Tom,

That was exactly what I needed to know and yes it does work.

I do know about the using/from clause and&nbsp; and the second table reference.

Sorry about the above example, I cut it from something much larger to try and
get my point&nbsp; across. Yes it is invalid. I should be more careful.

I do have another question I will post as a separate posting.

Gwyneth