The next example is an example of a searched UPDATE statement. Suppose you want to change the last name of employee 517. The Update statement might look like this:
UPDATE EMPDB.EMPLOYEE
SET LAST_NAME_0415 = 'ALLEN' WHERE EMP_ID_0415 = 517;
The UPDATE statement can change any field in the target record type. UPDATE can also be used to change the foreign keys in the table. This allows the user to change the department or office in which the employee works. Let's say employee Allen moves from the Westwood office, office code WST, to the Mt. Laurel office, office code MTL. An UPDATE statement could be formulated to execute this change:
UPDATE EMPDB.EMPLOYEE
SET OFFICE_CODE_0450 = 'MTL' WHERE EMP_LAST_NAME_0415 = 'ALLEN';
The table would make sure that there is an office (referential integrity check) with office code MTL before it updates the EMPLOYEE table for employee Allen. If more than one employee has Allen for a last name, the office code for each one is updated. From a table procedure standpoint, the program DISCONNECTs the employee from the OFFICE‑EMPLOYEE relationship, then locates office code MTL, and connects the employee to the new office.
Assume that employee Markey does not work in an office, but works from home. The database would not have the employee associated with any office. You can formulate an UPDATE statement that nulls his office code:
UPDATE EMPDB.EMPLOYEE
SET OFFICE_CODE_0450 = NULL WHERE EMP_LAST_NAME_0415 = 'MARKEY';
The table would now contain a NULL value in the OFFICE_CODE_0450 column of the EMPLOYEE table for all employees with the last name of Markey, which would, from a table procedure view, mean that the EMPLOYEE has been disconnected from the OFFICE‑EMPLOYEE set relationship. Note that to change foreign keys values, the set must have an OPTIONAL disconnect option. Because of this rule, the STRUCTURE table does not allow the user to change or null the MANAGER foreign key.
|
Copyright © 2013 CA.
All rights reserved.
|
|