InterBase Quick Start: Part III - Updating Data

From InterBase
Jump to: navigation, search

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

You 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 are not changed. 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 would increase the salary of salespeople by $2,000, by updating the salary column of the Employee table for rows where the value in the job_code column is “sales.” (Do not do this yet.)

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

Image 025.jpg Executing and Committing

  • For the rest of this tutorial, execute each statement after entering it. You will no longer be explicitly instructed to do so.
  • In addition, execute a COMMIT statement after executing a DML statement (INSERT, DELETE, UPDATE, and SELECT). DDL statements–CREATE, ALTER, and DROP–do not need manual commits because you have enabled Auto Commit DDL in IBConsole Interactive SQL Preferences.

Image 025.jpg Updating Data in the Employee Table

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

 UPDATE Employee
  SET salary = salary + 2000 
  WHERE job_code = 'Sales' AND hire_date < '01-JAN-1992'
A WHERE clause is not required for an update. If the previous statements did not include a WHERE clause, the update would increase the salary of all employees in the Employee table.

Image 025.jpg Running the Updates.sql Script

  1. Open the Updates.sql file in a text editor and look it over. It contains UPDATE statements that set values for the mngr_no column in the Department table, it updates the Customer table by setting the status of two customers to “on hold” by entering an asterisk in the on_hold column. Close the file when you have finished examining it.
  2. Choose Query > Execute and run the Updates.sql file.

Time to back up: If you have successfully run the Updates.sql script and performed the manual update, this is a good time to back up your database to Tutorial5.gbk.

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 a column is nullable, meaning that it is not defined as NOT NULL. Suppose that in the previous example, the department number of salespeople hired before 1992 is changing but the new number is not yet known. You would update salaries and department numbers as follows:

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. Suppose you want to change the manager of the department that Katherine Young current manages. One way to do this is to first determine Katherine Young’s department number (do not do this yet):

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

This query returns “623” as the department. Then, using 623 as the search condition in an UPDATE, you change the department with the following statement:

 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 one in which a SELECT clause is used within the WHERE clause to determine which rows to update.

Image 025.jpg Updating Department Using a Subquery

  1. In order to see the results of this exercise, begin by entering the following query to show you the manager number of Katherine’s department before you make the update:
    SELECT mngr_no FROM department WHERE dept_no = '623'
    

    This returns 15. (If you select first_name and last_name from the Employee table where emp_no equals 15, you will see that the manager of department 623 is Katherine herself.)

  2. Enter and execute the following UPDATE statement with a subquery to simultaneously find out Katherine’s department number and assign a new manager number to that department:
    UPDATE Department
         SET mngr_no = 107
         WHERE dept_no = (SELECT dept_no FROM Employee
         WHERE full_name = 'Young, Katherine')
    

    The rows returned by the SELECT statement within the parentheses are the rows that the UPDATE statement acts on.

  3. Now run the query in step 1 again to see the change. The manager of department 623 is manager number 107, rather than 15.
  4. This is not a change we want to keep, so enter and execute the following statement to reinstate Katherine Young as manager of department 623.
    UPDATE Department SET mngr_no = 15 WHERE dept_no = '623'
    

Advance To: