DELETE with JOIN syntax

On Wed, Jul 27, 2005 at 15:28:36 -0400,
Brian Wong <bwlist@gmail.com> wrote:

I am currently migrating from MySQL to PostgreSQL and I have found
that some queries do not work. For instance,

DELETE t1 FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL;

works in MySQL. This works as expected even though the MySQL
documentation does not mention the option of having a table between
the keywords DELETE and FROM.

I am trying to achieve the same affect for PostgreSQL so I tried

DELETE FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL;

and it did not work. Can someone explain to me exactly what is wrong
with this syntax?
Is a table expression produced by the JOIN allowed for a DELETE?
Im thinking that this would not work because the table expression is
not a real table and it would not make sense for DELETE to accept such
a parameter. How can I rewrite this query to achieve the same affect?
Thanks.

In 8.1 you will be able to use 'USING' to do this or something like it.
For now, I don't think you can use explicit join syntax and need to do
something like:
DELETE FROM t1 WHERE t1.column_in NOT IN (SELECT column_id FROM T2);
This assumes there aren't any NULL values in t2.column_id. If there are,
you can rewrite the above to use NOT EXISTS.

On Wed, 27 Jul 2005, Brian Wong wrote:

I am currently migrating from MySQL to PostgreSQL and I have found
that some queries do not work. For instance,

DELETE t1 FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL;

works in MySQL. This works as expected even though the MySQL
documentation does not mention the option of having a table between
the keywords DELETE and FROM.

I am trying to achieve the same affect for PostgreSQL so I tried

DELETE FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL;

and it did not work. Can someone explain to me exactly what is wrong
with this syntax?

It's mostly that AFAIK SQL has no equivalent syntax.

Is a table expression produced by the JOIN allowed for a DELETE?
Im thinking that this would not work because the table expression is
not a real table and it would not make sense for DELETE to accept such
a parameter. How can I rewrite this query to achieve the same affect?

I think the where t2.column_id is null where column_id is the joining
column makes this a form of not exists, so maybe:

DELETE FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.column_id =
t1.columnid);

On 7/27/05, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:

I think the where t2.column_id is null where column_id is the joining
column makes this a form of not exists, so maybe:

DELETE FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.column_id =
t1.columnid);

This looks good. Thanks.