pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDELETE 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