Re: Delete from Join

From: Gwyneth Morrison <gwynethm(at)toadware(dot)ca>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Delete from Join
Date: 2008-07-04 14:00:14
Message-ID: 486E2CEE.4010508@toadware.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:
<blockquote cite="mid:2938(dot)1215098986(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">Gwyneth Morrison <a class="moz-txt-link-rfc2396E" href="mailto:gwynethm(at)toadware(dot)ca">&lt;gwynethm(at)toadware(dot)ca&gt;</a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">What I am actually trying to get past is:
</pre>
</blockquote>
<pre wrap=""><!---->
</pre>
<blockquote type="cite">
<pre wrap=""> DELETE FROM data_table1
using data_table2 INNER JOIN
data_table1 ON data_table1.fkey =
data_table2.pkey;
</pre>
</blockquote>
<pre wrap=""><!---->
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
</pre>
</blockquote>
Thank you Tom,<br>
<br>
That was exactly what I needed to know and yes it does work.<br>
<br>
I do know about the using/from clause and&nbsp; and the second table
reference.<br>
<br>
Sorry about the above example, I cut it from something much larger to
try and <br>
get my point&nbsp; across. Yes it is invalid. I should be more careful.<br>
<br>
I do have another question I will post as a separate posting. <br>
<br>
Gwyneth<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 1.8 KB

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Gwyneth Morrison 2008-07-04 14:01:41 Re: xml and postgresql
Previous Message Cyril SCETBON 2008-07-04 13:57:08 Re: redundants indexes can be created