RE: DELETE FROM tableA WHERE NOT IN tableB ...

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