Date: Tue, 1 Jul 2003 09:46:03 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Robert Treat <rtreat@webmd.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: help with "delete joins"
Message-ID: <20030701144603.GA2410@wolff.to>
Mail-Followup-To: Robert Treat <rtreat@webmd.net>,
	pgsql-sql@postgresql.org
References: <1057011998.24310.348.camel@camel>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1057011998.24310.348.camel@camel>
User-Agent: Mutt/1.5.4i

On Mon, Jun 30, 2003 at 18:26:38 -0400,
  Robert Treat <rtreat@webmd.net> wrote:
> 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) ;

For the case without not (which appears to be what you really want)
you can pretty much do this.

See below for sample input and output.

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

delete from foo where foo.a = bar.a and foo.b=bar.b and
foo.c=bar.c;

select * from foo; 

CREATE TABLE
CREATE TABLE
INSERT 92443 1
INSERT 92444 1
INSERT 92445 1
INSERT 92446 1
INSERT 92447 1
INSERT 92448 1
INSERT 92449 1
INSERT 92450 1
DELETE 3
 a | b | c | d 
---+---+---+---
 1 | 2 | 4 | A
 4 | 5 | 6 | b
(2 rows)