Re: SQl update with Sub Select

This is V5R4 and later - not available in earlier releases.

But this is great stuff!

-------------- Original message -------------- 
From: "Joe D" <nike@xxxxxxxxxxxxxx> 

----- Original Message ----- 
From: "BirgittaHauser" 
If you are already on release V5R4 the following syntax should work: 
UPDATE phdmass01/STCHSP 
SET R9CEAA = 'N' 
WHERE (r9nmid, Field2, Field3, ... FieldN) in (SELECT (r9nmid, Field2x, 
Field3x ... FieldNx) 
from union/phdmassd) 

Wow.... 

Thanks Birgitta! 

I did not realize you could use the IN subselect coded like that. In the 
past I have always concatenated the fields on both sides to do the 
comparison. This looks a LOT cleaner. I am guessing it should also be 
faster if appropriate logicals exist for the subselected fields. 

JD 

-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l.