RE: DELETE FROM tableA WHERE NOT IN tableB ... - Mailing list pgsql-sql
| From | Michael Ansley |
|---|---|
| Subject | RE: DELETE FROM tableA WHERE NOT IN tableB ... |
| Date | |
| Msg-id | 2D50E16224C8D311B183009027452B47449CF8@INTEC003 Whole thread Raw |
| In response to | DELETE FROM tableA WHERE NOT IN tableB ... (The Hermit Hacker <scrappy@hub.org>) |
| List | pgsql-sql |
<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>