UPDATE: Modifying Table Data

Updating a single column of a single row

One of the most common update situations is when you need to modify just one column of a single row. Assigning a salesperson to a recently created customer, canceling an order, changing a product price — these are all examples of such a procedure. The following example assigns a price to product 990 that we previously created when we discussed INSERT in this chapter:

UPDATE product
SET prod_price_n = 18.24
WHERE prod_id_n = 990

Updating multiple columns

Sometimes you might want to update more than one column within one UPDATE statement. For example, imagine the manufacturer has changed the packaging for its product 990, so the dimensions are now 5 ×7 instead of 4 ×6. This update statement synchronizes the database information with the real-world change:

UPDATE product
SET prod_pltwid_n = 5,
    prod_pltlen_n = 7
WHERE prod_id_n = 990

Updating a column in all rows

Even though updating all table rows is not very typical (and often undesirable), sometimes you might want to perform such an operation. Giving all employees a 5 percent raise, inactivating all customers, setting all column values to NULL — these are a few common examples. As you could have noticed, the keyword here is "all." In other words, we would only want to omit the WHERE clause intentionally if we wanted to update each and every single row in the target table.

The UPDATE statement below increases all product prices by 10 percent (ACME, Inc. struggles with the increased operation costs):

UPDATE product
SET prod_price_n = prod_price_n * 1.1

Updating column using a single-row subquery

You can use the result of a SELECT statement (subquery) as an assignment value in an UPDATE statement. The main thing to remember is that your subquery must return no more than one row. (If no rows are returned, the NULL value will be assigned to the target column.) Also, according to SQL99 standards, only one expression can be specified in the select list.

You can concatenate two or more columns or perform math operations on them, but you can not list multiple columns separated with commas. Thus, SET my_col = (SELECT col1 + col2 ...) is valid, but SET my_col = (SELECT col1, col2 ...) is not.

Deriving the assignment value from another value

There are many situations when using a subquery as an assignment value is beneficial. For example, you want to change the payment terms for order 30670 to be N21531 in the ACME database. The problem is, in our relational database we do not store the actual value N21531 in an ORDER_HEADER table column; instead, we use the foreign key, which is a meaningless integer, from the PAYMENT_TERMS table. Using a subquery helps us to accomplish the task:

UPDATE order_header
SET ordhdr_payterms_fn =
      (SELECT payterms_id_n
       FROM payment_terms
       WHERE payterms_code_s = 'N21531')
WHERE ordhdr_id_n = 30670

Figure 6-1 illustrates the above example.

Figure 6-1: Using data from other table as an assignment value

The statement above has two WHERE clauses, but don't be confused: the first one belongs to the SELECT statement — as indicated by the surrounding parentheses, limiting the resulting set to one value — the primary key for the row where the value of payterms_code_s column is equal to N21531; the second WHERE clause belongs to the UPDATE statement and ensures that only one row of ORDER_HEADER with ordhdr_id_n equal to 30670 is updated.

Update with correlated subquery

The previous example was relatively straightforward — you derived the value you needed for the update from another given value. But sometimes conditions are more complicated. For example, imagine that ACME's business rules have changed and no longer allow orders to have payment terms different from the default payment terms of a customer who placed the order. You can relate (join) a table from the UPDATE clause with tables specified in the assignment subquery — that pretty much overrides the "single-row" rule because the assignment will be done on a row-by-row basis:

UPDATE order_header 
SET ordhdr_payterms_fn = 
     (SELECT payterms_id_n
      FROM   payment_terms, 
             customer
      WHERE  payterms_id_n = cust_paytermsid_fn 
      AND    ordhdr_custid_fn = cust_id_n) 

The very last line of this syntax joins the ORDHDR_CUSTID_FN field of the ORDER_HEADER table (UPDATE statement) with the CUST_ID_N table of the CUSTOMER table (nested subquery); in other words, the customer id field is the link between the UPDATE statement and the subquery that correlates them.

Note

You don't have to use table aliasing here because of the special notation rules you used when the ACME database was created. Each column is already prefixed with its table name abbreviation, so there are no ambiguous column names.

Cross-References

More discussion about table aliasing can be found in Chapters 8 and 9.

For each row in ORDER_HEADER you must find the corresponding value in the resulting set of the subquery and use it as an assignment value. The concept is illustrated in Figure 6-2.

Figure 6-2: Updating multiple rows using correlated subquery

Note

This syntax uses "old" join notation, which is recognized by all our "big three" databases. We used it here because in our opinion it better illustrates the correlated query concept. The SQL99-compliant equivalent syntax would be:

UPDATE order_header 
SET    ordhdr_payterms_fn = 
        (SELECT payterms_id_n
         FROM payment_terms 
         JOIN customer
         ON payterms_id_n = cust_paytermsid_fn 
         JOIN order_header
         ON ordhdr_custid_fn = cust_id_n)

The main differences between the "old" and the "new" join syntaxes are discussed in Chapter 9.

Note

Subqueries can also be used in the WHERE clause of the UPDATE statement in a similar way to the WHERE clause of the SELECT statement. We are going to discuss subqueries in general and correlated subqueries in particular in Chapter 8.

UPDATE statement and integrity constraints

Updating table rows obeys rules and restrictions similar to ones with INSERT statement. All column values have to be of the same or compatible data types and sizes with corresponding column definitions and no integrity constraints should be violated. There is a slight difference in behavior with the referential integrity constraints — when constraint is specified with ON UPDATE CASCADE or ON UPDATE SET NULL, RDBMS successfully performs an update of the target table; child tables' columns are also updated with the corresponding values.

Oracle 9i and DB2 8.1

The main thing that differs between Oracle and DB2's UPDATE syntax and that of our generic one is the option to specify values enclosed into the brackets and separated with commas in the SET clause and specify multiple corresponding assignment values for them. The syntax is

SET (col1, col2,... colN) = (value1, value2,... valueN)

The advantage of this syntax is in ability to use a multicolumn subquery instead of the list of values:

SET (col1, col2,... colN) = 
      (SELECT value1, value2,... valueN
       FROM...)

The subquery still has to return no more than one row.

MS SQL Server 2000

The UPDATE statement has an optional FROM clause in MS SQL Server. It specifies the table(s) to be used to provide the criteria for the update operation and can be used in a very similar way to the previously discussed correlated query. The following example performs virtually the same task as the correlated query from the previous section; the only difference is that when you use the former syntax, each and every row of ORDER_HEADER is updated unconditionally, whereas MS SQL Server syntax excludes columns with nulls unless the OUTER JOIN was used (more about joins in Chapter 9):

UPDATE order_header 
SET    ordhdr_payterms_fn = payterms_id_n
FROM   payment_terms JOIN   customer
ON     payterms_id_n = cust_paytermsid_fn JOIN order_header 
ON     ordhdr_custid_fn = cust_id_n