第3节数据处理之增删改.ppt

Updating Rows You can modify existing rows by using the UPDATE statement. In the syntax: table is the name of the table column is the name of the column in the table to populate value is the corresponding value or subquery for the column condition identifies the rows to be updated and is composed of column names expressions, constants, subqueries, and comparison operators Confirm the update operation by querying the table to display the updated rows. For more information, see Oracle9i SQL Reference, “UPDATE.” Note: In general, use the primary key to identify a single row. Using other columns can unexpectedly cause several rows to be updated. For example, identifying a single row in the EMPLOYEES table by name is dangerous, because more than one employee may have the same name. Updating Rows (continued) The UPDATE statement modifies specific rows if the WHERE clause is specified. The slide example transfers employee 113 (Popp) to department 70. If you omit the WHERE clause, all the rows in the table are modified. SELECT last_name, department_id FROM copy_emp; Note: The COPY_EMP table has the same data as the EMPLOYEES table. … Integrity Constraint Error If you attempt to update a record with a value that is tied to an integrity constraint, an error is returned. In the example on the slide, department number 55 does not exist in the parent table, DEPARTMENTS, and so you receive the parent key violation ORA-02291. Note: Integrity constraints ensure that the data adheres to a predefined set of rules. A subsequent lesson covers integrity constraints in greater depth. Instructor Note Explain integrity constraints, and review the concepts of primary key and foreign key. Removing a Row from a Table The slide graphic removes the Finance department from the DEPARTMENTS table (assuming that there are no constraints defined on the DEPARTMENTS table). Instructor Note After all the rows have been eliminated with the DELET