Categories
Code Talking

More AS400 SQL

I'm sure everyone else already knows this stuff, but since I use my blog for reference when I don't feel like trying to look things up, I'm going to do another piece on SQL. Specifically, another piece on SQL subselects….

I’m sure everyone else already knows this stuff, but since I use my blog for reference when I don’t feel like trying to look things up, I’m going to do another piece on SQL. Specifically, another piece on SQL subselects.

This time I needed to update a field, instead of deleting from a file. So the subselect was used in place of a join or something else that would just be too much work.

UPDATE targetfile SET targetfield = (select sourcefield from sourcefile where targetfile.targetfield = sourcefile.sourcefield) where checkfield = checkvalue

If you use multiple fields to perform your join, simply separate them with an and: targetfile.targetfieldone = sourcefile.sourcefieldone and targetfile.targetfieldtwo = sourcefile.sourcefieldtwo. That’s all there is to it!