Date: Mon, 30 Jun 2003 15:36:47 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Robert Treat <rtreat@webmd.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: help with "delete joins"
In-Reply-To: <1057011998.24310.348.camel@camel>
Message-ID: <20030630153445.C40392-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On 30 Jun 2003, Robert Treat wrote:

> create table foo (a int, b int, c int, d text);
>
> create table bar (a int, b int, c int);
>
> insert into foo values (1,2,3,'a');
> insert into foo values (1,2,4,'A');
> insert into foo values (4,5,6,'b');
> insert into foo values (7,8,9,'c');
> insert into foo values (10,11,12,'d');
>
> insert into bar values (1,2,3);
> insert into bar values (7,8,9);
> insert into bar values (10,11,12);
>
> what i want to do is:
>
> delete * from foo where not (foo.a = bar.a and foo.b=bar.b and
> foo.c=bar.c) ;
>
> so i end up with
>
> postgres=# select * from foo;
>  a | b | c | d
> ---+---+---+---
>  1 | 2 | 4 | A
>  4 | 5 | 6 | b
> (2 rows)
>
> but thats not valid sql, is there some way to accomplish this?

Maybe something like:

delete from foo where exists (select * from bar where
 bar.a=foo.a and bar.b=foo.b and bar.c=foo.c);