Changing data in a table using the UPDATE statement

To update data in a table or view, use the UPDATE statement.

With the UPDATE statement, you can change the value of one or more columns in each row that meets the search condition of the WHERE clause. The result of the UPDATE statement is one or more changed column values in zero or more rows of a table (depending on how many rows meet the search condition specified in the WHERE clause). The UPDATE statement looks like this:

   UPDATE table-name
     SET column-1 = value-1,
         column-2 = value-2, ...
     WHERE search-condition ...

Suppose that an employee is relocated. To update the CORPDATA.EMPLOYEE table to reflect the move, run the following statement:

   UPDATE CORPDATA.EMPLOYEE
     SET JOB = :PGM-CODE,
         PHONENO = :PGM-PHONE
     WHERE EMPNO = :PGM-SERIAL

Use the SET clause to specify a new value for each column that you want to update. The SET clause names the columns that you want updated and provides the values that you want them changed to. You can specify the following types of values:

• A column name. Replace the column's current value with the contents of another column in the same row.
• A constant. Replace the column's current value with the value provided in the SET clause.
• A null value. Replace the column's current value with the null value, using the keyword NULL. The column must be defined as capable of containing a null value when the table was created, or an error occurs.
• A host variable. Replace the column's current value with the contents of a host variable.
• A global variable. Replace the column's current value with the contents of a global variable.
• A special register. Replace the column's current value with a special register value; for example, USER.
• An expression. Replace the column's current value with the value that results from an expression.
• A scalar fullselect. Replace the column's current value with the value that the subquery returns.
• The DEFAULT keyword. Replace the column's current value with the default value of the column. The column must have a default value defined for it or allow the NULL value, or an error occurs.

The following UPDATE statement uses many different values:

   UPDATE WORKTABLE
      SET COL1 = 'ASC',
          COL2 = NULL,
          COL3 = :FIELD3,
          COL4 = CURRENT TIME,
          COL5 = AMT - 6.00,
          COL6 = COL7
       WHERE EMPNO = :PGM-SERIAL

To identify the rows to be updated, use the WHERE clause:

• To update a single row, use a WHERE clause that selects only one row.
• To update several rows, use a WHERE clause that selects only the rows you want to update.

You can omit the WHERE clause. If you do, SQL updates each row in the table or view with the values you supply.

If the database manager finds an error while running your UPDATE statement, it stops updating and returns a negative SQLCODE. If you specify COMMIT(*ALL), COMMIT(*CS), COMMIT(*CHG), or COMMIT(*RR), no rows in the table are changed (rows already changed by this statement, if any, are restored to their previous values). If COMMIT(*NONE) is specified, any rows already changed are not restored to previous values.

If the database manager cannot find any rows that meet the search condition, an SQLCODE of +100 is returned.

The SET clause of an UPDATE statement can be used in many ways to determine the actual values to be set in each row being updated. The following example lists each column with its corresponding value:

UPDATE EMPLOYEE
  SET WORKDEPT = 'D11',
      PHONENO = '7213',
      JOB = 'DESIGNER'
  WHERE EMPNO = '000270'

You can also write this UPDATE statement by specifying all of the columns and then all of the values:

UPDATE EMPLOYEE
  SET (WORKDEPT, PHONENO, JOB)
      = ('D11', '7213', 'DESIGNER')
  WHERE EMPNO = '000270'