Thread: 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;
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
----- 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.
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
>
<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>