User's Guide

Part II. Tutorials

Chapter 10. Updating the Database

In most databases, you need to update data stored in the database. For example, suppose that the employee named James Klobucher (employee ID 467) is transferred from the sales department to the marketing department. In SQL, this is done using the UPDATE statement:

     UPDATE employee
     SET dept_id = 400
     WHERE emp_id = 467

The WHERE clause identifies which employee to update.

Example: using the WHERE clause

SQL can update more than one column at a time. For example, the manager ID should change when employees are transferred between departments, as well as the department ID. The following statement carries out both updates at the same time for employee Marc Dill (employee ID 195):

     UPDATE employee
     SET dept_id = 400,
     manager_id = 1576
     WHERE emp_id = 195

The UPDATE and INSERT commands

The UPDATE and INSERT commands are two of the few places in SQL Anywhere where uppercase letters and lowercase letters are distinguished. New character values set by the UPDATE command are stored in the database exactly as they are entered.

SQL allows more than one row to be updated at one time. For example, if a group of sales employees are transferred into marketing and have their dept_id column updated, the following statement sets the manager_id for all employees in the marketing department to 1576.

     UPDATE employee
     SET manager_id = 1576
     WHERE dept_id = 400

For employees already in the marketing department, no change is made.

It is also possible that an UPDATE statement updates no rows. For example, suppose you had made a mistake typing the employee ID in the first UPDATE statement above:

     UPDATE employee
     SET dept_id = 400
     WHERE emp_id = 194

No rows would be updated since there is no employee with the employee ID 194.