| From: | Gwyneth Morrison <gwynethm(at)toadware(dot)ca> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Delete from Join |
| Date: | 2008-07-02 19:12:59 |
| Message-ID: | 486BD33B.5070304@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>
</head>
<body bgcolor="#ffffff" text="#000000">
<div class="moz-text-plain" wrap="true" graphical-quote="true"
style="font-family: -moz-fixed; font-size: 12px;" lang="x-western">
<pre wrap="">
--- On Wed, 7/2/08, Gwyneth Morrison <a class="moz-txt-link-rfc2396E"
href="mailto:postgres(at)toadware(dot)ca"><postgres(at)toadware(dot)ca></a> wrote:
</pre>
<blockquote type="cite" style="color: rgb(0, 0, 0);">
<pre wrap=""><span class="moz-txt-citetags">> </span>From: Gwyneth Morrison <a
class="moz-txt-link-rfc2396E" href="mailto:postgres(at)toadware(dot)ca"><postgres(at)toadware(dot)ca></a>
<span class="moz-txt-citetags">> </span>Subject: [GENERAL] Delete from Join
<span class="moz-txt-citetags">> </span>To: <a
class="moz-txt-link-abbreviated"
href="mailto:pgsql-general(at)postgresql(dot)org">pgsql-general(at)postgresql(dot)org</a>
<span class="moz-txt-citetags">> </span>Date: Wednesday, July 2, 2008, 3:15 PM
<span class="moz-txt-citetags">> </span>Hello,
<span class="moz-txt-citetags">> </span>
<span class="moz-txt-citetags">> </span>Is it possible to use a join keyword in a delete?
<span class="moz-txt-citetags">> </span>
<span class="moz-txt-citetags">> </span>For example:
<span class="moz-txt-citetags">> </span>
<span class="moz-txt-citetags">> </span> DELETE FROM data_table1
<span class="moz-txt-citetags">> </span> using data_table2 INNER JOIN
<span class="moz-txt-citetags">> </span> data_table1 ON data_table1.fkey =
<span class="moz-txt-citetags">> </span>data_table2.pkey;
<span class="moz-txt-citetags">> </span>
<span class="moz-txt-citetags">> </span>
<span class="moz-txt-citetags">> </span>It is not directly mentioned in the delete syntax but the
<span class="moz-txt-citetags">> </span>delete refers
<span class="moz-txt-citetags">> </span>to the select clause where JOIN is valid.
<span class="moz-txt-citetags">> </span>
<span class="moz-txt-citetags">> </span>G
<span class="moz-txt-citetags">> </span>
</pre>
</blockquote>
<pre wrap=""><!---->
>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,
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
<div class="moz-txt-sig">--
Sent via pgsql-general mailing list (<a class="moz-txt-link-abbreviated"
href="mailto:pgsql-general(at)postgresql(dot)org">pgsql-general(at)postgresql(dot)org</a>)
To make changes to your subscription:
<a class="moz-txt-link-freetext"
href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a>
</div></pre>
</div>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 3.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Matt Magoffin | 2008-07-02 19:54:49 | Re: Memory use in 8.3 plpgsql with heavy use of xpath() |
| Previous Message | John DeSoi | 2008-07-02 18:56:04 | changing text search treatment of puncutation |