Can you help me to write an SQL statement for the below scenario?
Main file : record number, customer code
Tempo file: record number
correct customer code
I need a SQL statement that will be able to update the main file using record numbers in both files?
Wouldn't it be great if one could simply type:
Update mainFile set customerCode = tempoFile.customerCode from tempoFile where mainFile.recordNumber = tempoFile.recordNumber
I have seen such SQL syntax somewhere on the Web but unfortunately, it is not available on the iSeries 400 at this stage. I am afraid some programming is required. I have managed to do the following. I am sure there are better ways but here it goes:
Create procedure myLib/testit ( ) language SQL begin declare realCustomer char (10); declare modifiedRow char (10); DECLARE not_found CONDITION FOR SQLSTATE '02000'; DECLARE at_end INT DEFAULT 0; declare c1 cursor for select tempfile.customer, permfile.recordNumber from tempfile, permfile where tempfile.recordNumber= permfile.recordNumber; DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; open c1; fetch c1into realCustomer, modifiedRow; WHILE at_end = 0 DO update permfile set customer = realCustomer where recordNumber = modifiedRow; fetch c1 into realCustomer, modifiedRow; END WHILE; CLOSE c1; END
Dig Deeper on DB2 UDB (universal databases)
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.