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

DELETE FROM tableA WHERE NOT IN tableB ...

From
The Hermit Hacker
Date:
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 



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

From
"Moray McConnachie"
Date:
----- Original Message -----
From: "The Hermit Hacker" <scrappy@hub.org>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, April 18, 2000 2:23 PM
Subject: [SQL] DELETE FROM tableA WHERE NOT IN tableB ...


>
> 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;

Not quite sure if I've understood your setup (you've got one too many
TableB's in the descrn, I think!)
but what about
DELETE FROM referer_data WHERE NOT EXISTS (SELECT referer_id FROM
referer_link) WHERE referer_link.referer_id=referer_data.referer_id);

or swop the referer_links and referer_datas.



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

From
Date:
not tested:
delete from table_a         where not exists (select * from 
table_b where breferer = areferer)

tested:
select br_coid from brokers where not exists (select * from
coidinfo where c_coid = br_coid) 

Kai

On Tue, 18 Apr 2000, The Hermit Hacker
wrote
> 
> 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 
> 



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

From
Michael Ansley
Date:
<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>