Обсуждение: help with "delete joins"

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 | A4 | 5 | 6 | b
(2 rows)

but thats not valid sql, is there some way to accomplish this? 

Robert Treat

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 wherebar.a=foo.a and bar.b=foo.b and bar.c=foo.c);

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

Robert,

> 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?

Um, your example result doesn't match your pseudo-query.  Assuming that you
want to delete everything that DOES match, not everything that DOESN'T, do:

DELETE FROM foo
WHERE EXISTS ( SELECT bar.a FROM barWHERE bar.a = foo.a AND bar.b = foo.b    AND bar.c = foo.c );


--
-Josh BerkusAglio Database SolutionsSan Francisco

On Mon, 2003-06-30 at 20:35, Josh Berkus wrote:
> Robert,
> 
> > 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? 
> 
> Um, your example result doesn't match your pseudo-query. 

the end of a long day that started with 4 hours of sleep... no wonder I
couldn't get my head around this one. I actually did want the results of
the psuedo query, not the results I posted :-\

> Assuming that you 
> want to delete everything that DOES match, not everything that DOESN'T, do:
> 
> DELETE FROM foo 
> WHERE EXISTS ( SELECT bar.a FROM bar
>     WHERE bar.a = foo.a AND bar.b = foo.b
>         AND bar.c = foo.c );

I was almost there with my original query... a NOT on your/stephan's 
query gets me what I really want. :-)  Thanks guys. 

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

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 3a | b | c | d 
---+---+---+---1 | 2 | 4 | A4 | 5 | 6 | b
(2 rows)