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.
Contents
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 thatKatherine 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 theUPDATE
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 manager107
(not15
). - This is not a change we want to keep, so enter the following statement to reinstate
Katherine Young
as the manager of the department623
.UPDATE Department SET mngr_no = 15 WHERE dept_no = '623'