Typically, the values of data items stored in a database are modified when corresponding changes occur in the outside world. For example, in the sample database:
- When a customer calls to change the quantity on an order, the QTY column in the appropriate row of the ORDERS table must be modified.
- When a manager moves from one office to another, the MGR column in the OFFICES table and the REP_OFFICE column in the SALESREPS table must be changed to reflect the new assignment.
- When sales quotas are raised by 5 percent in the New York sales office, the QUOTA column of the appropriate rows in the SALESREPS table must be modified.
In each case, data values in the database are updated to maintain the database as an accurate model of the real world. The smallest unit of data that can be modified in a database is a single column of a single row.
1. The update Statement
The UPDATE statement, shown in Figure 10-6, modifies the values of one or more columns in selected rows of a single table. The target table to be updated is named in the statement, and you must have the required permission to update the table as well as each of the individual columns that will be modified. The WHERE clause selects the rows of the table to be modified. The SET clause specifies which columns are to be updated and calculates the new values for them.
Here is a simple UPDATE statement that changes the credit limit and salesperson for a customer:
Raise the credit limit for Acme Manufacturing to $60,000 and reassign them to Mary Jones (employee number 109).
UPDATE CUSTOMERS
SET CREDIT_LIMIT = 60000.00, CUST_REP = 109
WHERE COMPANY = ‘Acme Mfg.’
1 row updated.
In this example, the WHERE clause identifies a single row of the CUSTOMERS table, and the SET clause assigns new values to two of the columns in that row. The WHERE clause is exactly the same one you would use in a DELETE or SELECT statement to identify the row. In fact, the search conditions that can appear in the WHERE clause of an UPDATE statement are exactly the same as those available in the SELECT and DELETE statements.
Like the DELETE statement, the UPDATE statement can update several rows at once with the proper search condition, as in this example:
Transfer all salespeople from the Chicago office (number 12) to the New York office (number 11), and lower their quotas by 10 percent.
UPDATE SALESREPS
SET REP_OFFICE = 11, QUOTA = .9 * QUOTA
WHERE REP_OFFICE = 12
3 rows updated.
In this case, the WHERE clause selects several rows of the SALESREPS table, and the value of the REP_OFFICE and QUOTA columns are modified in all of them. Conceptually, SQL processes the UPDATE statement by going through the SALESREPS table row by row, updating those rows for which the search condition yields a TRUE result and skipping over those for which the search condition yields a FALSE or NULL result.
Because it searches the table, this form of the UPDATE statement is sometimes called a searched UPDATE statement. This term distinguishes it from a different form of the UPDATE statement, called a positioned UPDATE statement, which always updates a single row. The positioned UPDATE statement applies only to programmatic SQL and is described in Chapter 17.
Here are some additional examples of searched UPDATE statements:
Reassign all customers served by employee numbers 105,106, or 107 to employee number 102.
UPDATE CUSTOMERS
SET CUST_REP = 102
WHERE CUST_REP IN (105, 106, 107)
5 rows updated.
Assign a quota of $100,000 to any salesperson who currently has no quota.
UPDATE SALESREPS
SET QUOTA = 100000.00
WHERE QUOTA IS NULL
1 row updated.
The SET clause in the UPDATE statement is a list of assignments separated by commas. Each assignment identifies a target column to be updated and specifies how to calculate the new value for the target column. Each target column should appear only once in the list; there should not be two assignments for the same target column. The ANSI/ISO specification mandates unqualified names for the target columns, but some SQL implementations allow qualified column names. There can be no ambiguity in the column names anyway, because they must refer to columns of the target table.
The expression in each assignment can be any valid SQL expression that yields a value of the appropriate data type for the target column. The expression must be computable based on the values of the row currently being updated in the target table. In most DBMS implementations, the expression may not include any column functions or subqueries.
If an expression in the assignment list references one of the columns of the target table, the value used to calculate the expression is the value of that column in the current row before any updates are applied. The same is true of column references that occur in the WHERE clause. For example, consider this (somewhat contrived) UPDATE statement:
UPDATE OFFICES
SET QUOTA = 400000.00, SALES = QUOTA
WHERE QUOTA < 400000.00
Before the update, Bill Adams had a QUOTA value of $350,000 and a SALES value of $367,911. After the update, his row has a SALES value of $350,000, not $400,000. The order of the assignments in the SET clause is thus immaterial; the assignments can be specified in any order.
2. Updating All Rows
The WHERE clause in the UPDATE statement is optional. If the WHERE clause is omitted, then all rows of the target table are updated, as in this example:
Raise all quotas by 5 percent.
UPDATE SALESREPS
SET QUOTA = 1.05 * QUOTA
10 rows updated.
Unlike the DELETE statement, in which the WHERE clause is almost never omitted, the UPDATE statement without a WHERE clause performs a useful function. It basically performs a bulk update of the entire table, as demonstrated in the preceding example.
3. Update with Subquery *
As with the DELETE statement, subqueries can play an important role in the UPDATE statement because they let you select rows to update based on information contained in other tables. Here are several examples of UPDATE statements that use subqueries:
Raise by $5000 the credit limit of any customer who has placed an order for more than $25,000.
UPDATE CUSTOMERS
SET CREDIT_LIMIT = CREDIT_LIMIT + 5000.00
WHERE CUST_NUM IN (SELECT DISTINCT CUST
FROM ORDERS
WHERE AMOUNT > 25000.00)
4 rows updated.
Reassign all customers served by salespeople whose sales are less than 80 percent of their quota.
UPDATE CUSTOMERS
SET CUST_REP = 105
WHERE CUST_REP IN (SELECT EMPL_NUM
FROM SALESREPS
WHERE SALES < (.8 * QUOTA))
2 rows updated.
Have all salespeople who serve over three customers report directly to Sam Clark (employee number 106).
UPDATE SALESREPS
SET MANAGER = 106 WHERE 3 < (SELECT COUNT(*)
FROM CUSTOMERS
WHERE CUST REP = EMPL NUM)
1 row updated.
As in the DELETE statement, subqueries in the WHERE clause of the UPDATE statement can be nested to any level and can contain outer references to the target table of the UPDATE statement. The column EMPL_NUM in the subquery of the preceding example is such an outer reference; it refers to the EMPL_NUM column in the row of the SALESREPS table currently being checked by the UPDATE statement. The subquery in this example is a correlated subquery, as described in Chapter 9.
Outer references will often be found in subqueries of an UPDATE statement, because they implement the join between the table(s) in the subquery and the target table of the UPDATE statement. The same SQL1 restriction applies as for the DELETE statement: the target table cannot appear in the FROM clause of any subquery at any level of nesting. This prevents the subqueries from referencing the target table (some of whose rows may have already been updated). Any references to the target table in the subqueries are thus outer references to the row of the target table currently being tested by the UPDATE statement’s WHERE clause. The SQL2 standard again removed this restriction and specifies that a reference to the target table in a subquery is evaluated as if none of the target table had been updated.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.