Re: Delete from Join

[Gwyneth Morrison <gwynethm@xxxxxxxxxxx>]

Tom Lane wrote:

Gwyneth Morrison <gwynethm@xxxxxxxxxxx> 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  and the second table reference.

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

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

Gwyneth