Data Manipulation with SQL › Data Manipulation Operations › Modifying Data

Modifying Data

Using UPDATE

You modify data in a table using an UPDATE statement. There are two types of UPDATE statement:

• If the WHERE clause contains a search condition, the statement modifies any row that meets the search condition—this is a searched update
• If the UPDATE statement specifies WHERE CURRENT OF cursor-name, the statement modifies only the row on which the cursor is positioned—this is a positioned update

Note: For information about positioned updates, see Using a Cursor.

Checking UPDATE Status

As with an INSERT statement, the DBMS enforces integrity constraints when the program issues an UPDATE statement.

Note: For more information about checking statement execution for constraint violation, see Adding Data.

Searched Updates

A searched update statement contains:

• A SET clause that specifies a value for each column to be updated
• A WHERE clause containing the criteria for choosing the rows to be updated

Searched Updates Using Host Variables

In this example, the UPDATE statement uses a host variable (SALARY-AMOUNT) to transfer a new data value to the database and another host variable (EMP-ID) supplies the column value that is the criterion for choosing the row to update:

EXEC SQL
   UPDATE POSITION
      SET SALARY_AMOUNT = :SALARY-AMOUNT
      WHERE EMP_ID = :EMP-ID
END-EXEC.

The statement in the example updates only one row because the search condition is restricted by the value of a primary key (EMP_ID).

The statement in the following example updates multiple rows if more than one employee does the job represented by the value in JOB-ID:

EXEC SQL
   UPDATE POSITION
      SET SALARY_AMOUNT = :SALARY-AMOUNT
      WHERE JOB_ID = :JOB-ID
END-EXEC.

Searched Updates Without Host Variables

A searched update may operate on existing column values without using host variables. This statement gives a 10 percent raise to all employees with a current salary in a specified range:

EXEC SQL
   UPDATE POSITION
      SET SALARY_AMOUNT = 1.1 * (SALARY_AMOUNT)
      WHERE SALARY_AMOUNT BETWEEN 20000 AND 40000
END-EXEC.

No Matching Rows

If no rows satisfy the selection criteria in the WHERE clause of a searched update, SQLCODE will be set to 100.

Automatic Rollback

If the attempt to update one row of a searched update fails:

• Statement execution halts
• The DBMS returns an error value to SQLCODE
• The results of the UPDATE statement are automatically rolled back