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

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(at)hub(dot)org]
>> Sent: 18 April 2000 14:23
>> To: pgsql-sql(at)postgresql(dot)org
>> Subject: [SQL] DELETE FROM tableA WHERE NOT IN tableB ...
>>
>>
>>
>>
>> > ----------
>> > From: The Hermit Hacker[SMTP:SCRAPPY(at)HUB(dot)ORG]
>> > Sent: Tuesday, April 18, 2000 3:23:24 PM
>> > To: pgsql-sql(at)postgresql(dot)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(at)hub(dot)org secondary:
>> scrappy(at){freebsd|postgresql}.org
>>