Re(3): SQL: UPDATE question

["Mike Naughton" <mnaughton@xxxxxxxxxxxx>]

MIDRANGE-L@midrange.com writes:
>You could probably do this:
>
>   UPDATE Table1 SET
>     fldOneC = (SELECT fldTwoE FROM Table2
>                WHERE KeyTwoA = KeyOneA
>                  AND KeyTwoB = KeyOneB),
>     fldOneD = (SELECT fldTwoF FROM Table2
>                WHERE KeyTwoA = KeyOneA
>                  AND KeyTwoB = KeyOneB)
>   WHERE EXISTS (SELECT * FROM Table2
>                 WHERE KeyTwoA = KeyOneA
>                   AND KeyTwoB = KeyOneB)
>
>That seems like a lot of thrashing though

Thanks, Pete -- that worked great! I agree with you that it's a bit
complicated, and it would be easier to do with embedded SQL (or, as Joe
pointed out, it would be quite simple with plain-old RPG). If this were
something I were doing over and over again (or giving to a user), I would
almost certainly use another method. But this was a one-shot deal -- a
bunch of records had to be changed, and I didn't see any reason why I
couldn't just do it using the interactive SQL screen. Sure, writing the
program wouldn't take very long, but I figured typing the proper statement
in SQL should be even faster (and cleaner -- no leftover objects that I'll
never use again). My other motive is that I want to learn more about SQL
-- if it's really the coming thing (as I keep hearing it is), then I want
to know how to do things like this (even if I know there may be a better
way). 

Thanks again for helping me learn something new today!

Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-863-4357 x444
mnaughton@juddwire.com

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---