InterBase Quick Start: Part III - Updating Data

Go Up to InterBase Quick Start: Part III - Populating the Database

You can use UPDATE statements to change values for one or more rows of data in existing tables.

Using Update

A simple update has the following syntax:

UPDATE table
SET    column = value
WHERE  CONDITION

The UPDATE statement changes values for columns specified in the SET clause. Columns not listed in the SET clause do not change. To update more than one column, list each column assignment in the SET clause, separated by a comma. The WHERE clause determines which rows to update. If there is no WHERE clause, all rows are updated. For example, the following statement increases the salary of salespeople by 2000, by updating the salary column of the Employee table for rows where the value in the job_code column is Sales (do not execute this statement).

UPDATE Employee
SET    salary = salary + 2000
WHERE  job_code = 'Sales'

Executing and Committing

Throughout the rest of this tutorial, you are prvided with a number of statements (some of those statements are DML statements). Remember to always do the following after entering a DML statement:

Execute the statement after entering it.
Commit your work.

Updating Data in the Employee Table

To make a more specific update, use a more restrictive WHERE clause. Enter the following statement to increase the salaries only for salespeople hired before January 1st, 1992:

UPDATE Employee
SET    salary = salary + 2000
WHERE  job_code = 'Sales'
       AND hire_date < '01-JAN-1992'

Without the WHERE clause in the statment above, the update would increase the salary of all employees in the Employee table.

Running the UPDATES.SQL Script

To perform the remaining updates, use an SQL script:

Load the UPDATES.SQL script.
Execute the query.

Time to Back up

If you have successfully executed the UPDATES.SQL script, it is a good time to back up your database.

Updating to a NULL Value

Sometimes data needs to be updated before all the new values are available. You can indicate unknown data by setting values to NULL. This works only if the column is not defined as NOT NULL (the column is nullable).

For example, if the department number of salespeople hired before 1992 is changing but the new number is not yet known, you would specify the dept_no as NULL:

UPDATE Employee
SET    salary = salary + 2000,
       dept_no = NULL
WHERE  job_code = 'Sales'
       AND hire_date < '01-Jan-1992'

Using a Subquery to Update

The search condition of a WHERE clause can be a subquery. For example, you want to change the manager of the department that Katherine Young currently manages. One way to do that is to first determine department number of Katherine Young:

SELECT dept_no
FROM   Employee
WHERE  full_name = 'Young, Katherine'

This query returns the value 632. You can use that result as the search condition in an UPDATE, to change the manager of the department:

UPDATE Department
SET    mngr_no = 107
WHERE  dept_no = '623'

A more efficient way to perform the update is to combine the two previous statements using a subquery. A subquery is a query in which you use a SELECT clause within the WHERE clause.

Updating the Department Table Using a Subquery

Begin by entering the following query to find out the manager number of the department that Katherine manages:
```
SELECT mngr_no
FROM   Department
WHERE  dept_no = '623'
```
The result is 15. You can double-check this result using the following query:
```
SELECT first_name, last_name
FROM   Employee
WHERE  emp_no = 15
```
The result is Katherine Young.
Enter and execute the following UPDATE statement with a subquery to simultaneously find out the department number that Katherine Young manages and assign a new manager to that department:
```
UPDATE Department
SET    mngr_no = 107
WHERE  dept_no = (SELECT dept_no
                  FROM   Employee
                  WHERE  full_name = 'Young, Katherine')
```
The rows that the SELECT statement within the parentheses returns are the rows that the UPDATE statement acts on.
Now run the first query in step 1 again to confirm the change. The manager of the department 623 is now the manager 107 (not 15).
This is not a change we want to keep, so enter the following statement to reinstate Katherine Young as the manager of the department 623.
```
UPDATE Department
SET    mngr_no = 15
WHERE  dept_no = '623'
```

Advance To:

Deleting Data