pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Ansley <Michael.Ansley@intecsystems.co.uk>
To: 'The Hermit Hacker' <scrappy@hub.org>
To: pgsql-sql@postgresql.org
Subject: RE: DELETE FROM tableA WHERE NOT IN tableB ...
Date: Tue, 18 Apr 2000 15:06:00 +0100
Message-ID: <2D50E16224C8D311B183009027452B47449CF8@INTEC003> (raw)

Can't you do something like:

DELETE FROM referer_data WHERE referer_id NOT IN (SELECT referer_id FROM
referer_link);

Or can't we do sub-selects in anything other than SELECT statements?  Or am
I misunderstanding what you are trying to do?

MikeA



>>   -----Original Message-----
>>   From: The Hermit Hacker [mailto:scrappy@hub.org]
>>   Sent: 18 April 2000 14:23
>>   To: pgsql-sql@postgresql.org
>>   Subject: [SQL] DELETE FROM tableA WHERE NOT IN tableB ...
>>   
>>   
>>   
>>   
>>   > ----------
>>   > From: 	The Hermit Hacker[SMTP:SCRAPPY@HUB.ORG]
>>   > Sent: 	Tuesday, April 18, 2000 3:23:24 PM
>>   > To: 	pgsql-sql@postgresql.org
>>   > Subject: 	[SQL] DELETE FROM tableA WHERE NOT IN tableB ...
>>   > Auto forwarded by a Rule
>>   > 
>>   > 
>>   And now for todays trick question ...
>>   
>>   have two tables, one of them is simple a string and a 
>>   serial value, the
>>   second one is the serial value and more data ...
>>   
>>   I want to clean out all records from tableB older then 
>>   date (that is
>>   easy), then I want to clean out all values from tableB 
>>   where there is no
>>   corresponding record in tableB ...
>>   
>>   basically tableA's serial field is unique, but tableB's could have
>>   multiple records associated with. 
>>   
>>   basically, what i've tried to do in a SELECT is something like:
>>   
>>   SELECT referer_id 
>>     FROM referer_data 
>>   EXCEPT 
>>         SELECT distinct(referer_id) FROM referer_link;
>>   
>>   But after 15 minutes, that's still running, so obviously 
>>   that won't work ...
>>   
>>   I can do it "in perl", but would love to come up with a 
>>   nice, elegant, 'in
>>   server' method of doing this instead :)
>>   
>>   Thanks...
>>   
>>   Marc G. Fournier                   ICQ#7615664             
>>     IRC Nick: Scrappy
>>   Systems Administrator @ hub.org 
>>   primary: scrappy@hub.org           secondary: 
>>   scrappy@{freebsd|postgresql}.org 
>>   

view thread (4+ messages)  latest in thread

Message-ID: <2D50E16224C8D311B183009027452B47449CF8@INTEC003>
Permalink:  ../2D50E16224C8D311B183009027452B47449CF8@INTEC003/
Also on:    postgresql.org/message-id/2D50E16224C8D311B183009027452B47449CF8@INTEC003

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: Michael.Ansley@intecsystems.co.uk, scrappy@hub.org
  Subject: Re: DELETE FROM tableA WHERE NOT IN tableB ...
  In-Reply-To: <2D50E16224C8D311B183009027452B47449CF8@INTEC003>
  References: <2D50E16224C8D311B183009027452B47449CF8@INTEC003>

* 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