Received: from oxmail.ox.ac.uk (oxmail1.ox.ac.uk [129.67.1.1]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA26760 for ; Tue, 18 Apr 2000 10:02:15 -0400 (EDT) (envelope-from moray.mcconnachie@computing-services.oxford.ac.uk) Received: from ermine.ox.ac.uk ([163.1.2.13]) by oxmail.ox.ac.uk with esmtp (Exim 3.12 #3) id 12hYZu-0002dv-00; Tue, 18 Apr 2000 15:02:10 +0100 Received: from ermine.ox.ac.uk ([163.1.2.13] helo=moraypc ident=root) by ermine.ox.ac.uk with smtp (Exim 3.13 #1) id 12hYZw-0005Ap-00; Tue, 18 Apr 2000 15:02:12 +0100 Message-ID: <006701bfa93e$801f61b0$760e01a3@oucs.ox.ac.uk> From: "Moray McConnachie" To: "The Hermit Hacker" , References: Subject: Re: DELETE FROM tableA WHERE NOT IN tableB ... Date: Tue, 18 Apr 2000 15:00:48 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200004/180 ----- Original Message ----- From: "The Hermit Hacker" To: 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.