AS400 SQL - Updating a file based on contents of a driver file
Posted on 2013-01-17
I'm trying to generate 2 SQL strings to run in an AS400 STRSQL command. First one is to update existing records based on the contents of a driver file and the second one is to actually insert new records based on a driver file. I've always struggled with the correct syntax and really want the procedures to be very resource efficient (There's a lot of records!).
So <MASTERFILE1> holds records which need to be updated with field data contained in <DRIVER1>. An example of file format for both files would be:
Field 1 (Key field)
Field 2 (Key field)
Field 3 (Key field)
Field 4 (Key field)
Field 5 (Key field)
Field 6 (data to update)
Field 7 (data to update)
If you were tackling this, would you simply say:
UPDATE MASTERFILE1 SET Field6=DRIVER1.Field6 WHERE ...... i got lost at this bit :(
The second query is simply to insert all records from DRIVER2 file (Same formats as before) into MASTERFILE1. I'm really unsure about how to even start on this.
My knowledge of SQL is basic in terms of, I can do select statements, nested selects etc but when I need to update records based on others... i'm lost :(