AS400 SQL Update statement

If you need to update a file on the AS400 using STRSQL, the statement is fairly straightforward.  Here's a few examples of how to do this:

UPDATE LIB1/FILE1 SET FIELD1='AAA'

This statement will update the file called FILE1 in library LIB1 to have the value of AAA in field1.  This will update every record in the file to contain that value. 

UPDATE FILE1 SET FIELD1='AAA'

This is the same as the statement above, except that it does not specify the library name.  Using the SQL this way, means that it will file the first instance of FILE1 in your library list and update that file.

UPDATE FILE1 SET FIELD1='AAA' WHERE FIELD2='BBB'

This statement will update all records in file name FILE1 to have the value of AAA in FIELD1 when the FIELD2 value is equal to BBB in the record.

UPDATE FILE1 SET FIELD1=123 WHERE FIELD2>123

This statement will update all records in file name FILE1 to have the value of 123 when FIELD2 is greater than 123.  In this case, both of these fields are defined as numeric in the file, so there are no single quotes around the data.