Subject: Re: help with "delete joins"
From: Robert Treat <xzilla@users.sourceforge.net>
To: Robert Treat <rtreat@webmd.net>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1057011998.24310.348.camel@camel>
References: <1057011998.24310.348.camel@camel>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 30 Jun 2003 18:40:22 -0400
Message-Id: <1057012822.24304.360.camel@camel>
Mime-Version: 1.0

On Mon, 2003-06-30 at 18:26, 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? 
> 

ok, i have a solution from the other end:

create table baz as select * from foo except (select foo.* from foo,bar
where foo.a = bar.a and foo.b=bar.b and foo.c=bar.c);

but i'd still be interested in a delete based method :-)

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL