UPDATE

The UPDATE statement is used to change information ina table. Here is how it is used:

update  customer
set     phone  =   “5185557012”
where   cust_id  =   2

(1 row(s) affected)

There are three parts to the UPDATE statement:First, list the table the UPDATE is affecting. Next, listthe columns that will be changed and their new values. Last,optionally, list a way to identify the rows. This example searchesthrough the customer table, looking for a row that has a customerID of 2. When it finds a row that matches, it updates the phonecolumn to the new value.

If you issue an UPDATE statement with no WHEREclause, the UPDATE affects the entire table. Here are somecolumns from the author table in the pubs database:

select au_lname, city
from authors

au_lname                                 city
---------------------------            ----------
White                                  Menlo Park
Green                                  Oakland
Carson                                 Berkeley
O’Leary                                San Jose
Straight                               Oakland
Smith                                  Lawrence
Bennet                                 Berkeley
Dull                                   Palo Alto
Gringlesby                             Covelo
Locksley                               San Francisco
Greene                                 Nashville
Blotchet-Halls                         Corvallis
Yokomoto                               Walnut Creek
del Castillo                           Ann Arbor
DeFrance                               Gary
Stringer                               Oakland
MacFeather                             Oakland
Karsen                                 Oakland
Panteley                               Rockville
Hunter                                 Palo Alto
McBadden                               Vacaville
Ringer                                 Salt Lake City
Ringer                                 Salt Lake City
(23 row(s) affected)

Now, if I run an UPDATE on the au3thors table and do notspecify which rows are updated, all rows will be updated:

update authors
set city  =  “Albany”
go
select au_lname, city from authors
go

(23 row(s) affected)
au_lname                                  city
-------------------                      --------
White                                    Albany
Green                                    Albany
Carson                                   Albany
O’Leary                                  Albany
Straight                                 Albany
Smith                                    Albany
Bennet                                   Albany
Dull                                     Albany
Gringlesby                               Albany
Locksley                                 Albany
Greene                                   Albany
Blotchet-Halls                           Albany
Yokomoto                                 Albany
del Castillo                             Albany
DeFrance                                 Albany
Stringer                                 Albany
MacFeather                               Albany
Karsen                                   Albany
Panteley                                 Albany
Hunter                                   Albany
McBadden                                 Albany
Ringer                                   Albany
Ringer                                   Albany
(23 row(s) affected)

When you use the UPDATE statement to change datain a table, pay attention to the rows affected. Almost always, youwant to affect just a single row, or a few rows in a large table.Be sure to use a WHERE clause to limit which rows areupdated.

---

TIP:  Updating a single row
If you want to update just a single row, try to specify theprimary key in the WHERE clause. In the customer table,the cust_id column is an identity column. Identity columnsguarantee that there will never be another row with the same value.

If I used fname and lname to update a row in customer, it isconceivable that two customers would have the same name, and theywould both be updated.

Ensuring that you update the right rows

If you want to be sure you’re going to affect the rightrows, before running an UPDATE, run a SELECT *using the WHERE clause from your UPDATEstatement.

---

Table Joins in Updates

MS SQL Server allows table joins in an UPDATEstatement, although this is not supported by the ANSI standard. To join tables in an UPDATE, you cannot use the ANSI joinsyntax. ANSI requires the use of a subquery to update a table basedon values in another table.

This example increases by five dollars the prices of all titlesby Stearns MacFeather:

update t
set t.price  =  t.price  +  $5
from titles  t, authors  a,  titleauthor  ta
where a.au_id  =  ta.au_id
      and  ta.title_id  =  t.title_id
      and  a.au_lname  =  “MacFeather”

(2 row(s) affected)

Stearns has written two books. I know that there is onlyone author in the authors table with the last name“MacFeather,” so I used the last name to identify thisauthor. (If there were several, I would have found the au_id Ineeded and used that to identify the author involved, instead ofau_lname.) To satisfy this UPDATE, the server finds therows in titles that join to MacFeather. It reads the price columnfor those rows, adds five to it, and writes the new value.