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

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