Postgres Pro
Downloads
Home   >   mailing lists

Thread: help with "delete joins"

help with "delete joins"

From
Robert Treat
Date:
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

Re: help with "delete joins"

From
Stephan Szabo
Date:
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);

Re: help with "delete joins"

From
Robert Treat
Date:
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

Re: help with "delete joins"

From
Josh Berkus
Date:
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

Re: help with "delete joins"

From
Robert Treat
Date:
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

Re: help with "delete joins"

From
Bruno Wolff III
Date:
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)