postgresql delete+join

almost always I think delete+join can be resolved with delete+exists

here's the source:

Re: DELETE with JOIN syntax

* From: Stephan Szabo
* To: Brian Wong
* Cc: pgsql-general(at)postgresql(dot)org
* Subject: Re: DELETE with JOIN syntax
* Date: Wed, 27 Jul 2005 13:14:13 -0700 (PDT)
* Message-id: <20050727130718.V16572@megazone.bigpanda.com>

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);