Received: from intec003.intecsystems.co.uk ([195.89.159.45]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA35768 for ; Tue, 18 Apr 2000 10:31:21 -0400 (EDT) (envelope-from Michael.Ansley@intecsystems.co.uk) Received: by INTEC003 with Internet Mail Service (5.5.2650.21) id ; Tue, 18 Apr 2000 15:06:01 +0100 Message-ID: <2D50E16224C8D311B183009027452B47449CF8@INTEC003> From: Michael Ansley To: "'The Hermit Hacker'" , pgsql-sql@postgresql.org Subject: RE: DELETE FROM tableA WHERE NOT IN tableB ... Date: Tue, 18 Apr 2000 15:06:00 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01BFA93F.3994CC70" X-Archive-Number: 200004/184 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BFA93F.3994CC70 Content-Type: text/plain; charset="iso-8859-1" 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 >> ------_=_NextPart_001_01BFA93F.3994CC70 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] DELETE FROM tableA WHERE NOT IN tableB ...

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 W= HERE NOT IN tableB ...
>>  
>>  
>>  
>>  
>>   > ----------
>>   > From:    The Hermit = Hacker[SMTP:SCRAPPY@HUB.ORG]
>>   > Sent:    Tuesday, Ap= ril 18, 2000 3:23:24 PM
>>   > To:      p= gsql-sql@postgresql.org
>>   > Subject:    &nb= sp;    [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 sim= ple a string and a
>>   serial value, the
>>   second one is the serial value and = more data ...
>>  
>>   I want to clean out all records fro= m 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 ...<= /FONT>
>>  
>>   basically tableA's serial field is = unique, but tableB's could have
>>   multiple records associated with. <= /FONT>
>>  
>>   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", bu= t would love to come up with a
>>   nice, elegant, 'in
>>   server' method of doing this instea= d :)
>>  
>>   Thanks...
>>  
>>   Marc G. Fournier   &= nbsp;           &nbs= p;   ICQ#7615664        &= nbsp;   
>>     IRC Nick: Scrappy
>>   Systems Administrator @ hub.org
>>   primary: scrappy@hub.org  = ;         secondary:
>>   scrappy@{freebsd|postgresql}.org
>>  

------_=_NextPart_001_01BFA93F.3994CC70--