InterBase Quick Start: Part III - Updating Data
You can use
UPDATE statements to change values for one or more rows of data in existing tables.
A simple update has the following syntax:
UPDATE table SET column = value WHERE CONDITION
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.
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'
WHERE clause in the statment above, the update would increase the salary of all employees in the
To perform the remaining updates, use an SQL script:
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
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
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
- 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
- Enter and execute the following
UPDATEstatement with a subquery to simultaneously find out the department number that
Katherine Youngmanages 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
SELECTstatement within the parentheses returns are the rows that the
UPDATEstatement acts on.
- Now run the first query in step 1 again to confirm the change. The manager of the department
623is now the manager
- This is not a change we want to keep, so enter the following statement to reinstate
Katherine Youngas the manager of the department
UPDATE Department SET mngr_no = 15 WHERE dept_no = '623'