Search Postgresql Archives

Re: DELETE with JOIN syntax

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Jul 27, 2005 at 15:28:36 -0400,
  Brian Wong <bwlist@xxxxxxxxx> 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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux