Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA99491 for ; Sat, 22 Jan 2000 14:39:14 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id OAA25487; Sat, 22 Jan 2000 14:37:39 -0500 (EST) From: Bruce Momjian Message-Id: <200001221937.OAA25487@candle.pha.pa.us> Subject: Re: [SQL] More efficient DELETE ... ? In-Reply-To: <20925.948564467@sss.pgh.pa.us> from Tom Lane at "Jan 22, 2000 01:07:47 pm" To: Tom Lane Date: Sat, 22 Jan 2000 14:37:39 -0500 (EST) CC: The Hermit Hacker , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL66 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > 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. Good point. I had forgotten all about that. Should I cover that in my book? What about the NOTICE we were going to add when this happens. You will see a WARN_FROM define in parser/*.c. I had to disable the message because the regression tests were failing. They have to be regenerated after I do this, but I can't do that here. Do people still want the message? Can someone regenerate the regression output after I am done? In fact, someone can just remove the #ifdef WARN_FROM defines around the code and regenerate the regression tests and all will stay in sync. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026