Skip site navigation (1) Skip section navigation (2)

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

From: "Moray McConnachie" <moray(dot)mcconnachie(at)computing-services(dot)oxford(dot)ac(dot)uk>
To: "The Hermit Hacker" <scrappy(at)hub(dot)org>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: DELETE FROM tableA WHERE NOT IN tableB ...
Date: 2000-04-18 14:00:48
Message-ID: 006701bfa93e$801f61b0$760e01a3@oucs.ox.ac.uk (view raw or flat)
Thread:
Lists: pgsql-sql
----- Original Message -----
From: "The Hermit Hacker" <scrappy(at)hub(dot)org>
To: <pgsql-sql(at)postgresql(dot)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.


In response to

pgsql-sql by date

Next:From: Tom LaneDate: 2000-04-18 14:04:02
Subject: Re: trouble with null
Previous:From: The Hermit HackerDate: 2000-04-18 13:23:24
Subject: DELETE FROM tableA WHERE NOT IN tableB ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group