From: | Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> |
---|---|
To: | Robert Treat <rtreat(at)webmd(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: help with "delete joins" |
Date: | 2003-06-30 22:40:22 |
Message-ID: | 1057012822.24304.360.camel@camel |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rudi Starcevic | 2003-06-30 23:48:01 | Re: Bitwise operation |
Previous Message | Stephan Szabo | 2003-06-30 22:36:47 | Re: help with "delete joins" |