Updating Rows
Updating information in a database row is done in much the same way that you would retrieve data using the SELECT statement. The UPDATE statement is used to actually modify data in a table. The syntax of the update statement is
UPDATE <tablename> | <viewname> | <alias>
SET <column> = <New value>[,
<column> = <New value>…]
[FROM <table list>]
[WHERE <Boolean constraint>]
There are three basic sections to the UPDATE statement:
The table that the UPDATE is modifying.
The list of columns that will be updated and their respective new values.
Optionally, you can specify multiple tables if joining tables together to limit the number of rows being updated.
The following example searches the entire customer_demo table looking for a row that has a customer ID of 2. When it finds a row that matches, it updates the city column to the new value. Before performing the update, we display the row of data; after completing the update, we display the data again.
Use northwind set nocount on SELECT cust_id, city FROM customer_demo WHERE cust_id = 2 UPDATE customer_demo SET city = 'Buffalo' WHERE cust_id = 2 SELECT cust_id, city FROM customer_demo WHERE cust_id = 2
Results:
cust_id city ----------- ---------------------------------------- 2 NULL cust_id city ----------- ---------------------------------------- 2 Buffalo
Caution
If you issue an UPDATE statement with no WHERE clause, the UPDATE updates the data in the entire table.
Tip
If you want to be sure that you are affecting the correct rows in the table, you should execute a SELECT * using the WHERE clause from your UPDATE statement before executing the UPDATE statement. I did this in the UPDATE example.
Updating Data Using Data in Other Tables
You have the ability to modify data in a table by using the information contained in other tables in the database. You can use data from one other table or, by using joins, you can use data from multiple tables.
This example (using the pubs database) will increase the prices of all titles by Stearns MacFeather by five dollars:
Use Pubs
update t
set t.price = t.price + $5
FROM authors as a inner join titleauthor as ta
on a.au_id = ta.au_id
inner join titles as t
on ta.title_id = t.title_id
where a.au_lname = 'MacFeather'
In previous releases of Microsoft SQL Server, joins were defined in the WHERE clause, you would not have been able to write the update statement using the new INNER JOIN syntax.
This author has written two books. I used the last name of the author to identify him in the authors table. To complete this UPDATE request, the server finds the rows in the titles table that join to 'MacFeather'. It then reads the price column for those rows, adds five to it, and then writes the new value back to the table.
Note
You might be asking what the titleauthor table is doing in this example. Well, the titles table does not have any columns in it that relate directly to the authors table. The titleauthor table is where this relationship is stored. The titles table joins to the titleauthor table, and then the titleauthor table joins to the authors table.
Caution
After you update a column, the change is permanent. There is no "UNDO" feature.
What? Well, that is not entirely correct. On Day 13, "Programming with Transactions," you will see how to use the transaction processing capability of the server to "UNDO" an update.
Reviewing the Limits on the UPDATE Statement
You may update only one table at a time. If you are joining tables in your update, you must be sure to update columns from only a single table. In addition, if you are updating a view, and that view references multiple tables, you must perform the update in as many steps as there are tables in the view.
Tip
If you are working with large tables, try to perform as many column changes as you can in a single UPDATE statement. If you change two columns in one update, the row must be read into memory and written back to the table only once. If you change one column in two separate UPDATE statements, each row must be read and written twice.