Re: [SQL] Using the IN predicate in an UPDATE...

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(at)mcs(dot)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(at)q8(dot)nrnet(dot)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