Update in SQL

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!


%{ ***************************************************** %}
%{ ************ 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 {
  %}
 %}
%}

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;     

If a cusror has been created, and the 'FOR UPDATE' clause has been included, the row will be locked for update, much the same as if a record from an file defined on an F-Spec had been read by the program. The row may be updated by an SQL update statement, using the WHERE CURRENT specifying the cursor name. Once the update has been issued the lock is released.

      //*=====================================================
      //* Procedure: declare()
      //*=====================================================
       dcl-Proc declare ;
         dcl-PI *N end-PI;

              exec sql declare CP2000 Cursor for
                            Select * from SCPDIVF0
                              For Update ;

       end-Proc declare ;

      //*=====================================================
      //* Procedure: updateC1()
      //*=====================================================
       dcl-Proc updateC1 ;
         dcl-PI *N end-PI;

              exec sql update SCPDIVF0 set CUPR00 = :CUPR00,
                                           CUPR05 = :CUPR00,
                                           CUPR06 = :CUPR00
                            where current of CP2000 ;

       end-Proc updateC1 ;