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.” (Don’t do this yet.)

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

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—don’t need manual commits because you have enabled Auto Commit DDL in IBConsole’s Interactive SQL Preferences.

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.

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.

Advance To:

Updating to a NULL Value