pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Thomas Good <tomg@admin.nrnet.org>
Cc: PostgreSQL List <pgsql-sql@postgreSQL.org>
Subject: Re: Using the IN predicate in an UPDATE...
Date: Wed, 14 Oct 1998 16:42:32 +0200
Message-ID: <l03110701b24a637bb536@[194.90.105.28]> (raw)
In-Reply-To: <Pine.LNX.3.96.981014085503.5064A-100000@admin.nrnet.org>
References: <l03110700b24a22c17e45@[194.90.105.243]>

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





view thread (6+ messages)  latest in thread

Message-ID: <l03110701b24a637bb536@[194.90.105.28]>
Permalink:  ../l03110701b24a637bb536@%5B194.90.105.28%5D/
Also on:    postgresql.org/message-id/l03110701b24a637bb536@[194.90.105.28]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, tomg@admin.nrnet.org
  Subject: Re: Using the IN predicate in an UPDATE...
  In-Reply-To: <l03110701b24a637bb536@[194.90.105.28]>
  References: <l03110700b24a22c17e45@[194.90.105.243]> <l03110701b24a637bb536@[194.90.105.28]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox