Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA74712 for ; Sat, 22 Jan 2000 13:08:07 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id NAA20928; Sat, 22 Jan 2000 13:07:48 -0500 (EST) To: The Hermit Hacker cc: pgsql-sql@postgresql.org Subject: Re: [SQL] More efficient DELETE ... ? In-reply-to: References: Comments: In-reply-to The Hermit Hacker message dated "Sat, 22 Jan 2000 13:20:06 -0400" Date: Sat, 22 Jan 2000 13:07:47 -0500 Message-ID: <20925.948564467@sss.pgh.pa.us> From: Tom Lane The Hermit Hacker writes: > This looks painful ... > DELETE FROM webhit_referer_raw > WHERE oid IN ( SELECT w.oid > FROM webhit_referer_raw w, referrer_data r > WHERE stat_date < 'Jan 17 2000 15:05:00' > AND w.referrer_url = r.referrer ); I believe you can do DELETE FROM webhit_referer_raw WHERE referrer_url = referrer_data.referrer AND referrer_data.stat_date < 'Jan 17 2000 15:05:00'; (I assume stat_date is in referrer_data, otherwise this'd be easy.) This is an example where Postgres' willingness to create implicit FROM clause entries is a win ... AFAIK you couldn't do it in standard SQL, since for some reason DELETE doesn't take a FROM clause. regards, tom lane