Modifying Rows Using the UPDATE Statement

You use the UPDATE statement to change rows in a table. When you typically use the UPDATE statement, you specify the following information:

• The table containing the rows that are to be changed

• A WHERE clause that specifies the rows that are to be changed

• A list of column names , along with their new values, specified using the SET clause

You can change one or more rows using the same UPDATE statement. If more than one row is specified, the same change will be implemented for all of those rows. The following statement updates the last_name column to Orange for the row whose customer_id is 2:

  UPDATE customers   SET last_name = 'Orange'   WHERE customer_id = 2;  1 row updated. 

SQL*Plus confirms that one row was updated. If the WHERE clause were omitted, all the rows would be updated. Notice that the SET clause is used in the UPDATE statement to specify the column and its new value. The following query confirms the change was made:

  SELECT *   FROM customers   WHERE customer_id = 2;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  2 Cynthia Orange 05-FEB-68 800-555-1212 

You can change multiple rows and multiple columns in the same UPDATE statement. For example, the following UPDATE raises the price by 20 percent for all products whose current price is greater than or equal to $20. The UPDATE also changes those products names to lowercase:

  UPDATE products   SET   price = price * 1.20,   name = LOWER(name)   WHERE   price  >  = 20;  3 rows updated. 

As you can see, three rows are updated by this statement. You can confirm the change using the following query:

  SELECT product_id, name, price   FROM products   WHERE price  >  = (20 * 1.20);  ID NAME PRICE ---------- ------------------------------ ----------  2 chemistry 36  3 supernova 31.19  5 z-files 59.99 

Note  

You can also use a subquery with an UPDATE statement. This was covered in Chapter 6 in the section Writing an UPDATE Statement Containing a Subquery.

The RETURNING Clause

In Oracle Database 10 g you can use the RETURNING clause to return the value from an aggregate function such as AVG() . Aggregate functions were covered in Chapter 3.

The following example performs the following tasks :

• Declares a variable named average_product_price

• Decreases the price column of the rows in the products table and saves the average price in the average_product_price variable using the RETURNING clause

• Rolls back the update

• Prints the value of the average_product_price variable

    VARIABLE average_product_price NUMBER   UPDATE products   SET price = price * 0.75   RETURNING AVG(price) INTO :average_product_price;  12 rows updated.  ROLLBACK;  Rollback complete.  PRINT average_product_price  AVERAGE_PRODUCT_PRICE ---------------------  14.7966667