Update on Field Test

Using SQL you can update a field in one file by using a field in another file where unique field matching is possible. In the example below, the statement will update all the load dates in FILE1 to contain the same load date as found in FILE2 matching the two files by company and claim number.

COALESCE covers the situation where one field may contain a NULL.

UPDATE FILE1 A 
   SET A.LOADDT = (SELECT COALESCE(B.LOADDT, A.LOADDT) FROM FILE2 B 
   WHERE A.COMP = B.COMP AND A.CLAIM = B.CLAIM) 

Note: The field to be updated must have the same attributes as the field updated from.