Examples

Example 1: Change the job (JOB) of employee number (EMPNO) '000290' in the EMPLOYEE table to 'LABORER'.

  UPDATE EMPLOYEE
    SET JOB = 'LABORER'
    WHERE EMPNO = '000290'

Example 2: Increase the project staffing (PRSTAFF) by 1.5 for all projects that department (DEPTNO) 'D21' is responsible for in the PROJECT table.

  UPDATE PROJECT
    SET PRSTAFF = PRSTAFF + 1.5
    WHERE DEPTNO = 'D21'

Example 3: All the employees except the manager of department (WORKDEPT) 'E21' have been temporarily reassigned. Indicate this by changing their job (JOB) to NULL and their pay (SALARY, BONUS, COMM) values to zero in the EMPLOYEE table.

  UPDATE EMPLOYEE
    SET JOB=NULL, SALARY=0, BONUS=0, COMM=0
    WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'

Example 4: In a Java(TM) program display the rows from the EMPLOYEE table on the connection context 'ctx' and then, if requested to do so, change the job (JOB) of certain employees to the new job keyed in (NEWJOB).

  #sql iterator empIterator implements sqlj.runtime.ForUpdate
       with( updateColumns='JOB' )
       ( ... );
  empIterator C1;

  #sql [ctx] C1 = { SELECT * FROM EMPLOYEE };

  #sql { FETCH :C1 INTO ... };
  while ( !C1.endFetch() )  {
     System.out.println( ... );
                 ...
     if ( condition for updating row ) {
         #sql [ctx] { UPDATE EMPLOYEE
                        SET JOB = :NEWJOB
                        WHERE CURRENT OF :C1 };
     }

     #sql { FETCH :C1 INTO ... };
  }
  C1.close();