pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas Good <tomg@admin.nrnet.org>
To: Leslie Mikesell <les@Mcs.Net>
Cc: herouth@oumail.openu.ac.il
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Using the IN predicate in an UPDATE...
Date: Thu, 15 Oct 1998 08:37:53 -0400 (EDT)
Message-ID: <Pine.LNX.3.96.981015083730.1204A-100000@admin.nrnet.org> (raw)
In-Reply-To: <199810150321.WAA24646@Jupiter.mcs.net>


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




view thread (6+ messages)  latest in thread

Message-ID: <Pine.LNX.3.96.981015083730.1204A-100000@admin.nrnet.org>
Permalink:  ../Pine.LNX.3.96.981015083730.1204A-100000@admin.nrnet.org/
Also on:    postgresql.org/message-id/Pine.LNX.3.96.981015083730.1204A-100000@admin.nrnet.org

 · 

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: tomg@admin.nrnet.org, les@Mcs.Net, herouth@oumail.openu.ac.il
  Subject: Re: [SQL] Using the IN predicate in an UPDATE...
  In-Reply-To: <Pine.LNX.3.96.981015083730.1204A-100000@admin.nrnet.org>
  References: <199810150321.WAA24646@Jupiter.mcs.net> <Pine.LNX.3.96.981015083730.1204A-100000@admin.nrnet.org>

* 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