The UPDATE Statement

3 4

The UPDATE statement is used to modify or update existing data. The basic syntax for the UPDATE statement is shown here:

 UPDATE table_name SET column_name = expression       [FROM table_source ]  WHERE search_condition 

Updating Rows

Building on our sample table, items, we'll first update the junk food row that we inserted earlier without a price. To identify the row, specify fried pork skins in the search condition. To set (update) the price to $2, use the following statement:

UPDATE items SET price = 2.00   WHERE item_desc = 'fried pork skins' GO

Now select the junk food row by using this query:

SELECT * FROM items   WHERE item_desc = 'fried pork skins' GO

The output for the junk food row appears as follows, with the original NULL value for price replaced by 2.00:

item_category item_id price item_desc  --------------- ------- --------- ---------------- junk food 2 2.00 fried pork skins

To increase the price of this item by 10 percent, you would run the following statement:

UPDATE items SET price = price * 1.10   WHERE item_desc = 'fried pork skins' GO

Now if you select the junk food row, you will notice the price has been changed to $2.20 ($2 multiplied by 1.10). The prices of the other items have not changed.

You can update more than one row by using an UPDATE statement. For example, to update all the rows in the items table by increasing their price values by 10 percent, run the following statement:

UPDATE items SET price = price * 1.10 GO

Now if you examine the items table, it will look like this:

tem_category item_id price item_desc  --------------- ------- --------- ---------------- health food 1 4.40 tofu 6 oz. junk food 2 2.42 fried pork skins toys 3 NULL No desc

Rows with a value of NULL for price will not be affected because NULL * 1.10 = NULL. This is not a problem; you will not get an error.

Using the FROM Clause

The UPDATE statement enables you to use the FROM clause to specify a table to be used as the source of data in an update. The table source list can include table names, view names, rowset functions, derived tables, and joined tables. Even the table that is being updated can be used as a table source. To see how this process works, let's first create another small sample table. The CREATE TABLE statement for our new table, named tax, and an INSERT statement to insert a row with a value of 5.25 for the tax_percent column are shown here:

 CREATE TABLE tax  ( tax_percent real NOT NULL, change_date smalldatetime DEFAULT getdate() ) GO INSERT INTO tax     (tax_percent) VALUES (5.25) GO 

The change_date column will get the current date and time from its default GETDATE function because a date was not explicitly inserted.

Next let's add a new, nullable column, price_with_tax, to our original items table, as shown here:

ALTER TABLE items  ADD price_with_tax smallmoney NULL GO 

Next we want to update the new price_with_tax column to contain the result of items.price * tax.tax_percent for all rows in the items table. To do so, use the following UPDATE statement with a FROM clause:

UPDATE items  SET price_with_tax = i.price + (i.price * t.tax_percent / 100) FROM items i, tax t GO

The two rows in the items table that have a value in the price column now have a calculated value in the price_with_tax column. The row that has a NULL for price is not affected, and its price_with_tax entry is NULL as well, because a null value multiplied by anything is NULL. The result set for all rows in the items table (including all of the modifications we have made so far) now looks like this:

item_category item_id price item_desc price_with_tax  --------------- ------- --------- ---------------- -------------- health food 1 4.40 tofu 6 oz. 4.63 junk food 2 2.42 fried pork skins 2.55 toys 3 NULL No desc NULL

If you add items to the table, you could run the preceding UPDATE statement again, but that would repeat the same update on rows that already have a value for price_with_tax, which would waste processing time. You can use the WHERE clause of the UPDATE statement so that only rows that currently have a NULL in the price_with_tax column are updated, as shown here:

UPDATE items  SET price_with_tax = i.price + (i.price * t.tax_percent / 100) FROM items i, tax t WHERE i.price_with_tax IS NULL GO

This UPDATE statement is a good candidate for a trigger that would be executed when a value is inserted into the price column. A trigger is a special type of stored procedure that is automatically executed upon certain conditions. Triggers will be discussed in detail in Chapter 22.

Using Derived Tables

Another way to use the UPDATE statement is with a derived table, or subquery, in the FROM clause. The derived table is used as input for the outer UPDATE statement. For this example, we will use the two_newest_items table in the subquery and the items table in the outer UPDATE statement. We want to update the two newest rows in the items table to contain a price_with_tax value of NULL. By querying the two_newest_items table, we can find the item_id values of the rows that need to be updated in the items table. The following statement achieves this:

UPDATE items  SET price_with_tax = NULL FROM (SELECT item_id FROM two_newest_items) AS t1 WHERE items.item_id = t1.item_id GO

The SELECT statement serves as a subquery, whose results are put into a temporary derived table called t1, which is then used in the search condition (the WHERE clause). The results from the subquery give us the item_id values 2 and 3. Thus, the two rows in the items table with an item_id column value of 2 or 3 are affected. The row with an item_id value of 3 already had a NULL in the price_with_tax column, so it does not change values. The row with an item_id value of 2 does get its price_with_tax value changed to NULL. The result set showing all rows in the items table after this update looks like this:

item_category item_id price item_desc price_with_tax  --------------- ------- --------- ---------------- ---------------- health food 1 4.40 tofu 6 oz. 4.63 junk food 2 2.42 fried pork skins NULL toys 3 NULL No desc NULL

MORE INFO

---

For details about additional options that can be used with the UPDATE statement, such as table hints and query hints, check the Books Online index for "UPDATE" and select the subtopic "Described."