Updating table data

You can change a column value to another value or remove the column value altogether.

About this task

To change the data in a table, use the UPDATE statement. You can also use the UPDATE statement to remove a value from a column (without removing the row) by changing the column value to null.

Example: Suppose that an employee relocates. To update several items of the employee's data in the YEMP work table to reflect the move, you can execute the following statement: 
UPDATE YEMP
  SET JOB = 'MANAGER ',
  PHONENO ='5678'
  WHERE EMPNO = '000400';

You cannot update rows in a created temporary table, but you can update rows in a declared temporary table.

The SET clause names the columns that you want to update and provides the values that you want to assign to those columns. You can replace a column value in the SET clause with any of the following items:

  • A null value

    The column to which you assign the null value must not be defined as NOT NULL.

  • An expression, which can be any of the following items:
    • A column
    • A constant
    • A scalar fullselect
    • A host variable
    • A special register
  • A default value

    If you specify DEFAULT, DB2® determines the value based on how the corresponding column is defined in the table.

In addition, you can replace one or more column values in the SET clause with the column values in a row that is returned by a fullselect.

Next, identify the rows to update:

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

If you omit the WHERE clause, DB2 updates every row in the table or view with the values that you supply.

If DB2 finds an error while executing your UPDATE statement (for example, an update value that is too large for the column), it stops updating and returns an error. No rows in the table change. Rows that were already changed, if any, are restored to their previous values. If the UPDATE statement is successful, SQLERRD(3) is set to the number of rows that are updated.

Example: The following statement supplies a missing middle initial and changes the job for employee 000200.
UPDATE YEMP
  SET MIDINIT = 'H', JOB = 'FIELDREP'
  WHERE EMPNO = '000200';

The following statement gives everyone in department D11 a raise of 400.00. The statement can update several rows. 

UPDATE YEMP
  SET SALARY = SALARY + 400.00
  WHERE WORKDEPT = 'D11';

The following statement sets the salary for employee 000190 to the average salary and sets the bonus to the minimum bonus for all employees. 

UPDATE YEMP
  SET (SALARY, BONUS) = 
  (SELECT AVG(SALARY), MIN(BONUS) 
    FROM EMP)
   WHERE EMPNO = '000190';
Parent topic: Adding and modifying data from application programs