Delete from Join

Started by Gwyneth Morrisonalmost 18 years ago8 messagesgeneral

Jump to latest

Gwyneth Morrison

postgres@toadware.ca

almost 18 years ago

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.

Lennin Caro

lennin.caro@yahoo.com

almost 18 years ago

In reply to: Gwyneth Morrison (#1)

Re: Delete from Join

--- On Wed, 7/2/08, Gwyneth Morrison <postgres@toadware.ca> wrote:

From: Gwyneth Morrison <postgres@toadware.ca>
Subject: [GENERAL] Delete from Join
To: pgsql-general@postgresql.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))

Gwyneth Morrison

gwynethm@toadware.ca

almost 18 years ago

In reply to: Lennin Caro (#2)

Re: Delete from Join

<!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@toadware.ca">&lt;postgres@toadware.ca&gt;</a> wrote:

</pre>
<blockquote type="cite" style="color: rgb(0, 0, 0);">
<pre wrap="">> From: Gwyneth Morrison <a
class="moz-txt-link-rfc2396E" href="mailto:postgres@toadware.ca"><postgres@toadware.ca></a>
> Subject: [GENERAL] Delete from Join
> To: <a
class="moz-txt-link-abbreviated"
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>
> 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
> 
</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.

<div class="moz-txt-sig">--
Sent via pgsql-general mailing list (<a class="moz-txt-link-abbreviated"
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.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>

Import Notes

Resolved by subject fallback

Scott Marlowe

scott.marlowe@gmail.com

almost 18 years ago

In reply to: Gwyneth Morrison (#3)

Re: Delete from Join

Take a look here, in the notes section:

http://www.postgresql.org/docs/8.3/interactive/sql-delete.html

on the using keyword.

Gwyneth Morrison

gwynethm@toadware.ca

almost 18 years ago

In reply to: Scott Marlowe (#4)

Re: Delete from Join

Scott Marlowe wrote:

Take a look here, in the notes section:

http://www.postgresql.org/docs/8.3/interactive/sql-delete.html

on the using keyword.

Thank you for your reply Scott,

I guess this is where the confusion started for me.

It says here in your reference that the using clause is not standard but
that is fine as I am doing a subst. MS actually allows:

DELETE from table from table.....

I change the second from to a using and it works fine most of the time.

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;

Where the INNER JOIN keyword is used in the delete.

In the documentation section you cited, they refer to the from clause
in the usinglist. The from clause link there refers to select which states
that a join keyword is valid in a from clause.

What I have found is it sometimes compiles but doesn't work.

I am just seeking verification if it is supposed to work.

Gwyneth

Lennin Caro

lennin.caro@yahoo.com

almost 18 years ago

In reply to: Gwyneth Morrison (#3)

Re: Delete from Join

--- On Wed, 7/2/08, Gwyneth Morrison <gwynethm@toadware.ca> wrote:

From: Gwyneth Morrison <gwynethm@toadware.ca>
Subject: Re: [GENERAL] Delete from Join
To: pgsql-general@postgresql.org
Date: Wednesday, July 2, 2008, 7:12 PM
--- On Wed, 7/2/08, Gwyneth Morrison
<postgres@toadware.ca> wrote:
From: Gwyneth Morrison <postgres@toadware.ca>
Subject: [GENERAL] Delete from Join
To: pgsql-general@postgresql.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,

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

i have the same problem. i try use JOIN keyword in DELETE syntax but dont work. I assume cant use JOIN keywork

Tom Lane

tgl@sss.pgh.pa.us

almost 18 years ago

In reply to: Gwyneth Morrison (#5)

Re: Delete from Join

Gwyneth Morrison <gwynethm@toadware.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

Gwyneth Morrison

gwynethm@toadware.ca

almost 18 years ago

In reply to: Tom Lane (#7)

Re: Delete from Join

<!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.1215098986@sss.pgh.pa.us" type="cite">
<pre wrap="">Gwyneth Morrison <a class="moz-txt-link-rfc2396E" href="mailto:gwynethm@toadware.ca"><gwynethm@toadware.ca></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;

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, 
 
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 
</body>
</html>