OCA/OCP Oracle Database 11g All-in-One Exam Guide in Software

Paint ANSI/AIM Code 39 in Software OCA/OCP Oracle Database 11g All-in-One Exam Guide

The following illustration shows the results of the exercise, using SQL*Plus:

The UPDATE command is used to change rows that already exist rows that have been created by an INSERT command, or possibly by a tool such as Data Pump As with any other SQL command, an UPDATE can affect one row or a set of rows The size of the set affected by an UPDATE is determined by a WHERE clause, in exactly the same way that the set of rows retrieved by a SELECT statement is defined by a WHERE clause The syntax is identical All the rows updated will be in one table; it is not possible for a single update command to affect rows in multiple tables When updating a row or a set of rows, the UPDATE command specifies which columns of the row(s) to update It is not necessary (or indeed common) to update every column of the row If the column being updated already has a value, then this value is replaced with the new value specified by the UPDATE command If the column was not previously populated which is to say, its value was NULL then it will be populated after the UPDATE with the new value A typical use of UPDATE is to retrieve one row and update one or more columns of the row The retrieval will be done using a WHERE clause that selects a row by its primary key, the unique identifier that will ensure that only one row is retrieved Then the columns that are updated will be any columns other than the primary key column It is very unusual to change the value of the primary key The lifetime of a row begins when it is inserted, then may continue through several updates, until it is deleted Throughout this lifetime, it will not usually change its primary key To update a set of rows, use a less restrictive WHERE clause than the primary key To update every row in a table, do not use any WHERE clause at all This set behavior can be disconcerting when it happens by accident If you select the rows to be updated with any column other than the primary key, you may update several rows, not just one If you omit the WHERE clause completely, you will update the whole table perhaps millions of rows updated with just one statement when you meant to change just one

EXAM TIP One UPDATE statement can change rows in only one table, but it can change any number of rows in that table An UPDATE command must honor any constraints defined for the table, just as the original INSERT would have For example, it will not be possible to update a column that has been marked as mandatory to a NULL value or to update a primary key column so that it will no longer be unique The basic syntax is the following:

UPDATE table SET column=value [,column=value] [WHERE condition];

The more complex form of the command uses subqueries for one or more of the column values and for the WHERE condition Figure 8-1 shows updates of varying complexity, executed from SQL*Plus The first example is the simplest One column of one row is set to a literal value Because the row is chosen with a WHERE clause that uses the equality predicate on the table s primary key, there is an absolute guarantee that at most only one row will be affected No row will be changed if the WHERE clause fails to find any rows at all The second example shows use of arithmetic and an existing column to set the new value, and the row selection is not done on the primary key column If the selection is not done on the primary key, or if a nonequality predicate (such as BETWEEN) is used, then the number of rows updated may be more than one If the WHERE clause is omitted entirely, the update will be applied to every row in the table The third example in Figure 8-1 introduces the use of a subquery to define the set of rows to be updated A minor additional complication is the use of a replacement variable to prompt the user for a value to use in the WHERE clause of the subquery