From: Josh Berkus <josh@agliodbs.com>
To: Robert Treat <rtreat@webmd.net>
To: pgsql-sql@postgresql.org
Subject: Re: help with "delete joins"
Date: Mon, 30 Jun 2003 17:35:29 -0700
Message-ID: <200306301735.29482.josh@agliodbs.com> (raw)
In-Reply-To: <1057011998.24310.348.camel@camel>
References: <1057011998.24310.348.camel@camel>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, rtreat@webmd.net
Subject: Re: help with "delete joins"
In-Reply-To: <200306301735.29482.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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