pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Using the IN predicate in an UPDATE...
6+ messages / 4 participants
[nested] [flat]

* Using the IN predicate in an UPDATE...
@ 1998-10-14 13:06  Thomas Good <tomg@admin.nrnet.org>
  1 sibling, 1 reply; 6+ messages in thread

From: Thomas Good @ 1998-10-14 13:06 UTC (permalink / raw)
  To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: pgsql-sql

Herouth,

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.

Thanks much!
Tom

    ---------- Sisters of Charity Medical Center ----------
                   Department of Psychiatry
                            ----     
    Thomas Good                          <tomg@q8.nrnet.org>
    Coordinator, North Richmond C.M.H.C. Information Systems
    75 Vanderbilt Ave, Quarters 8        Phone: 718-354-5528
    Staten Island, NY   10304            Fax:   718-354-5056





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Using the IN predicate in an UPDATE...
@ 1998-10-14 14:42  Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 1 reply; 6+ messages in thread

From: Herouth Maoz @ 1998-10-14 14:42 UTC (permalink / raw)
  To: Thomas Good <tomg@admin.nrnet.org>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Re: Using the IN predicate in an UPDATE...
@ 1998-10-14 20:27  Gene Selkov Jr. <selkovjr@mcs.anl.gov>
  parent: Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 0 replies; 6+ messages in thread

From: Gene Selkov Jr. @ 1998-10-14 20:27 UTC (permalink / raw)
  To: Thomas Good <tomg@admin.nrnet.org>; +Cc: pgsql-sql

> 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
>   );

I think the urge to do everything you might need inside the server can be disabling. I would export the table to a file, fix it (with perl, sed, whatever) and import it back again.

--Gene



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Using the IN predicate in an UPDATE...
@ 1998-10-15 03:21  Leslie Mikesell <les@Mcs.Net>
  parent: Thomas Good <tomg@admin.nrnet.org>
  0 siblings, 2 replies; 6+ messages in thread

From: Leslie Mikesell @ 1998-10-15 03:21 UTC (permalink / raw)
  To: tomg@admin.nrnet.org; +Cc: herouth@oumail.openu.ac.il; pgsql-sql

According to Thomas Good:
> 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
>   );     

Am I missing something here or is this the same as:

UPDATE table1 SET id = 2 WHERE id = 1;

??
  Les Mikesell
    les@mcs.com



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Using the IN predicate in an UPDATE...
@ 1998-10-15 12:37  Thomas Good <tomg@admin.nrnet.org>
  parent: Leslie Mikesell <les@Mcs.Net>
  1 sibling, 0 replies; 6+ messages in thread

From: Thomas Good @ 1998-10-15 12:37 UTC (permalink / raw)
  To: Leslie Mikesell <les@Mcs.Net>; +Cc: herouth@oumail.openu.ac.il; pgsql-sql


RE: optimizing this query... ;-)

> UPDATE table1 SET id = 2
> WHERE rec_num IN
>  ( SELECT rec_num
>    FROM table1
>    WHERE id = 1
>   );

1) Herouth, thanks...I aim to try this where I really need to do
table aliasing in an update (it will come up as I continue to port
the foxpro stuff...)  I know how to do it in a SELECT but was 
befuddled (per usual) re aliasing in an UPDATE.  Thanks again.

> 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

2)  Gene - you're my kind of guy...but I've been scolded so many times
by my perl buddies for being an incurable shell scripter that I really
do try to make SQL do the work whenever possible...thanks for the reply!

> I think the urge to do everything you might need inside the server 
> can be disabling. I would export the table to a file, fix it 
> (with perl, sed, whatever) and import it back again.
> --Gene

> Am I missing something here or is this the same as:
> UPDATE table1 SET id = 2 WHERE id = 1;
> ??
>  Les Mikesell
>    les@mcs.com

3) Les,  What are ya, some kinda smarta**?  Yeah, somebody was missing
something, but it weren't you...  ;-)

BTW, I never did get UnixWare to work right re UUCP via TCP (sending).
But receiving is happening so my Linux boxes poll the UW box to grab
files and exchange the mail.  Dodgy workaround but it is UnixWare, after
all...the original white elephant.

Thanks for your help (again...!)

Cheers,
Tom

    ---------- Sisters of Charity Medical Center ----------
                   Department of Psychiatry
                            ----     
    Thomas Good                          <tomg@q8.nrnet.org>
    Coordinator, North Richmond C.M.H.C. Information Systems
    75 Vanderbilt Ave, Quarters 8        Phone: 718-354-5528
    Staten Island, NY   10304            Fax:   718-354-5056




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Using the IN predicate in an UPDATE...
@ 1998-10-18 09:24  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Leslie Mikesell <les@Mcs.Net>
  1 sibling, 0 replies; 6+ messages in thread

From: Herouth Maoz @ 1998-10-18 09:24 UTC (permalink / raw)
  To: Thomas Good <tomg@admin.nrnet.org>; Leslie Mikesell <les@Mcs.Net>; +Cc: pgsql-sql

At 14:37 +0200 on 15/10/98, Thomas Good wrote:


> > Am I missing something here or is this the same as:
> > UPDATE table1 SET id = 2 WHERE id = 1;
> > ??
> >  Les Mikesell
> >    les@mcs.com
>
> 3) Les,  What are ya, some kinda smarta**?  Yeah, somebody was missing
> something, but it weren't you...  ;-)

Hmmm. Are you sure? The original query you presented had different
semantics. It meant "If the recnum has any occurence with id=1, change all
its occurences (even those with id=17) to id=2".

That was the reason why I suggested an "exists" query instead. The above is
only equivalent when each recnum has only one id.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~1998-10-18 09:24 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-14 13:06 ` Thomas Good <tomg@admin.nrnet.org>
1998-10-15 03:21   ` Leslie Mikesell <les@Mcs.Net>
1998-10-15 12:37     ` Thomas Good <tomg@admin.nrnet.org>
1998-10-18 09:24     ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-10-14 14:42 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-10-14 20:27   ` Gene Selkov Jr. <selkovjr@mcs.anl.gov>

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