Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.8.8/8.8.8) with ESMTP id KAA25606 for ; Wed, 14 Oct 1998 10:42:04 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from [194.90.105.28] (herouth@[194.90.105.28]) by shaked.cc.openu.ac.il (8.8.5/8.8.5) with ESMTP id QAA13912; Wed, 14 Oct 1998 16:41:10 +0200 (IST) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: References: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Wed, 14 Oct 1998 16:42:32 +0200 To: Thomas Good From: Herouth Maoz Subject: Re: Using the IN predicate in an UPDATE... Cc: PostgreSQL List At 15:06 +0200 on 14/10/98, Thomas Good wrote: > This query takes 20 years (poetic licence invoked ;-). > Do you have a smarter way to accomplish this end: > > UPDATE table1 SET id = 2 > WHERE rec_num IN > ( SELECT rec_num > FROM table1 > WHERE id = 1 > ); > > This is an attempt to cleanup some user error...I have an old > foxpro db that uses a char str as an index and this index has no > check constraints (like, making the index unique...ouch.) > The new pg db is performing nicely, now that it's live...and > housing 12 years worth of data. But I am saddled with quite a bit > of housekeeping - correcting anomalies that were part and parcel > of the original (foxpro) design. Or absence thereof. I didn't know I became an SQL guru... I don't even have the proper version of Postgres for subqueries... It all depends on what you have in mind. First, is this an operation you are likely to perform frequently, or is this a one-time fix you want to run on your system? Which fields are indexed? If the id field is indexed, it seems to me the above query should not be *that* lengthy. In any case, an EXISTS query is supposed to be more efficient. Especially if the only index is on rec_num. Try this: UPDATE table1 SET id = 2 WHERE EXISTS ( SELECT * FROM table1 t1 WHERE t1.rec_num = table1.rec_num AND id = 1 ); I hope the above table aliasing scopes rec_num correctly... I have no way of trying it myself, because, as I said, I don't have 6.3 as yet. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma