pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp with "delete joins"
6+ messages / 5 participants
[nested] [flat]
* help with "delete joins"
@ 2003-06-30 22:26 Robert Treat <rtreat@webmd.net>
0 siblings, 4 replies; 6+ messages in thread
From: Robert Treat @ 2003-06-30 22:26 UTC (permalink / raw)
To: pgsql-sql
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?
Robert Treat
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: help with "delete joins"
@ 2003-06-30 22:36 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Robert Treat <rtreat@webmd.net>
3 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2003-06-30 22:36 UTC (permalink / raw)
To: Robert Treat <rtreat@webmd.net>; +Cc: pgsql-sql
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 where
bar.a=foo.a and bar.b=foo.b and bar.c=foo.c);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: help with "delete joins"
@ 2003-06-30 22:40 Robert Treat <xzilla@users.sourceforge.net>
parent: Robert Treat <rtreat@webmd.net>
3 siblings, 0 replies; 6+ messages in thread
From: Robert Treat @ 2003-06-30 22:40 UTC (permalink / raw)
To: Robert Treat <rtreat@webmd.net>; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: help with "delete joins"
@ 2003-07-01 00:35 Josh Berkus <josh@agliodbs.com>
parent: Robert Treat <rtreat@webmd.net>
3 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2003-07-01 00:35 UTC (permalink / raw)
To: Robert Treat <rtreat@webmd.net>; pgsql-sql
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 bar
WHERE bar.a = foo.a AND bar.b = foo.b
AND bar.c = foo.c );
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: help with "delete joins"
@ 2003-07-01 13:43 Robert Treat <xzilla@users.sourceforge.net>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Robert Treat @ 2003-07-01 13:43 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: help with "delete joins"
@ 2003-07-01 14:46 Bruno Wolff III <bruno@wolff.to>
parent: Robert Treat <rtreat@webmd.net>
3 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2003-07-01 14:46 UTC (permalink / raw)
To: Robert Treat <rtreat@webmd.net>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-07-01 14:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-30 22:26 help with "delete joins" Robert Treat <rtreat@webmd.net>
2003-06-30 22:36 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-06-30 22:40 ` Robert Treat <xzilla@users.sourceforge.net>
2003-07-01 00:35 ` Josh Berkus <josh@agliodbs.com>
2003-07-01 13:43 ` Robert Treat <xzilla@users.sourceforge.net>
2003-07-01 14:46 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox