SQL Updates

Updating row and column data

The example below is an SQL function in Net.Data showing the a simple update operation. Notice the WHERE clause in the SQL UPDATE statement. It is the WHERE clause which specifies the record (or records) that will be updated. If the WHERE clause is omitted, all rows of the table will be updated!

SQL Update

%{ ***************************************************** %}
%{ ************ Update record ************************** %}
%{ ***************************************************** %}

%FUNCTION(DTW_SQL) UpdRecord(IN pCOD,pCODE,pDSC,pARA,pTYP) {

 Update $(tableName)

   set  STDSTS=$(pCODE),STDDSC='$(pDSC)',STDARA='$(pARA)',STDTYP='$(pTYP)',
	STDDTE=$(Sys_Date),STDTIM=$(Trn_Time),STDUSR='$(REMOTE_USER)',STDPGM='PNA00045'

  where STDSTS = $(pCOD)
	
        %message{
                100: "Record not found for update." : continue
                  0: "Record updated." : continue
        %}

 %report {
  %row {
  %}
 %}
%}

Updating from a table

The example below is slightly more complex. The update values are supplied by a subquery slecting the column values from another table. 

           Exec SQL                                                                                 
           Update VRWSTMTS set                                                                      
                ( SHBLNAME ,                                                                        
                  SHBLADDR1 ,                                                                       
                  SHBLADDR2 ,                                                                       
                  SHBLCSZ   ,                                                                       
                  SHBLSTATE ,                                                                       
                  SHBLZIP   ,                                                                       
                  SHHMADDR1 ,                                                                       
                  SHHMADDR2 ,                                                                       
                  SHHMCSZ   ,                                                                       
                  SHHMSTATE ,                                                                       
                  SSHMZIP   ) =                                                                     
             ( Select  TFNAME || ', ' || FMINI || '. ' || TLNAME ,                                  
                       TADDR1                                    ,                                  
                       TADDR2                                    ,                                  
                       TCITY || ' ' || TSTATE || ', ' || TZIP    ,                                  
                       TSTATE                                    ,                                  
                       TZIP                                      ,                                  
                       CADDR1                                    ,                                  
                       CADDR2                                    ,                                  
                       CCITY || ' ' || CSTATE || ', ' || CZIP    ,                                  
                       CSTATE                                    ,                                  
                       CZIP                                                                         
           from GPMCUST where shloan = tloan                                                        
               and shtxfer = ttxfer );                                                              
                                                                                                    
          EXEC SQL                                                                                  
             get diagnostics :rowsUpdated = db2_number_rows;

Updating from a column

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.

           Exec SQL                                                                                 
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.