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 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 UPDATE statement may have updated more than one row. The number of rows updated is reflected in SQLERRD(3) of the SQLCA. This value is also available from the ROW_COUNT diagnostics item in the GET DIAGNOSTICS statement.

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'

• Updating a table using a scalar-subselect
  Using a scalar-subselect, you can update one or more columns in a table with one or more values selected from another table.

• Updating a table with rows from another table
  You can update an entire row in one table with values from a row in another table.

• Updating tables with referential constraints
  If you are updating a parent table, you cannot modify a primary key for which dependent rows exist.

• Updating an identity column
  You can update the value in an identity column to a specified value or have the system generate a new value.

• Updating data as it is retrieved from a table
  You can update rows of data as you retrieve them by using a cursor.

 

Parent topic:

Data manipulation language

 

Related reference

UPDATE