pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
DELETE FROM tableA WHERE NOT IN tableB ...
4+ messages / 4 participants
[nested] [flat]

* DELETE FROM tableA WHERE NOT IN tableB ...
@ 2000-04-18 13:23  The Hermit Hacker <scrappy@hub.org>
  0 siblings, 2 replies; 4+ messages in thread

From: The Hermit Hacker @ 2000-04-18 13:23 UTC (permalink / raw)
  To: pgsql-sql


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 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: DELETE FROM tableA WHERE NOT IN tableB ...
@ 2000-04-18 14:00  Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  parent: The Hermit Hacker <scrappy@hub.org>
  1 sibling, 0 replies; 4+ messages in thread

From: Moray McConnachie @ 2000-04-18 14:00 UTC (permalink / raw)
  To: The Hermit Hacker <scrappy@hub.org>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: DELETE FROM tableA WHERE NOT IN tableB ...
@ 2000-04-18 14:06  Michael Ansley <Michael.Ansley@intecsystems.co.uk>
  0 siblings, 0 replies; 4+ messages in thread

From: Michael Ansley @ 2000-04-18 14:06 UTC (permalink / raw)
  To: 'The Hermit Hacker' <scrappy@hub.org>; pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: DELETE FROM tableA WHERE NOT IN tableB ...
@ 2000-04-18 14:15  kaiq@realtyideas.com
  parent: The Hermit Hacker <scrappy@hub.org>
  1 sibling, 0 replies; 4+ messages in thread

From: kaiq@realtyideas.com @ 2000-04-18 14:15 UTC (permalink / raw)
  To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql

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 
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2000-04-18 14:15 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-18 13:23 DELETE FROM tableA WHERE NOT IN tableB ... The Hermit Hacker <scrappy@hub.org>
2000-04-18 14:00 ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
2000-04-18 14:15 ` kaiq@realtyideas.com
2000-04-18 14:06 RE: DELETE FROM tableA WHERE NOT IN tableB ... Michael Ansley <Michael.Ansley@intecsystems.co.uk>

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