DB2 statement to update a header file if no corresponding record exists

Question

I have written below SQL in an RPGLE program. Intent is to update the header file (TC400F) if no corresponding records exist in detail file (TC401F). Are there better ways of doing this?

Exec SQL UPDATE TC400F 
 SET T40STS = '05', 
 T40OFL = '1' 
 WHERE T40SID = :K#T41SID AND 
 T40PID = :K#T41PID AND 
 NOT EXISTS (SELECT * FROM TC401F WHERE
 T41SID = :K#T41SID AND 
 T41PID = :K#T41PID ); 

• dnoeth: Does your version of DB2 support MERGE? ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/sqlp/…

Answer 1

Not really but you can simplify your query like this (variable are not replaced 2x)

Exec SQL UPDATE TC400F f1 
 SET (f1.T40STS, f1.T40OFL) = ('05', '1') 
 WHERE (f1.T40SID, f1.T40PID) = (:K#T41SID, :K#T41PID) AND 
 NOT EXISTS
 (SELECT * FROM TC401F f2 WHERE (f1.T40SID, f1.T40PID)=(f2.T41SID, f2.T41PID));

• jmarkmurphy: Did you mean (f2.T40SID, f2.T40PID) or (f2.T41SID, f2.T41PID) in the subselect?
• Esperento57: i have change my query, ty