Limits on the UPDATE Statement

Once you update a column, the change is permanent. There is no“undo” feature.

You may update only one table at a time. If you are joiningtables in your update, you must be sure to update columns from onlya single table. Likewise, if you are updating a view, and the viewreferences multiple tables, you must perform the UPDATEin two steps.

The titleview view in the pubs database joins titles, authors,and titleauthor together to list several columns from each table.Here is a sample of the output from the titleview view:

title                              au_ord au_lname price ytd_sales pub_id
---------------------------------- ------ -------- ----- --------- -----
The Busy Executive’s Database Guide 2     Green    19.99 4095      1389
The Busy Executive’s Database Guide 1     Bennet   19.99 4095      1389

If the Busy Executive’s book was sold to a different publisher (publisher ID 0736), and the publisher wanted to reversethe order of the author’s names, here is an UPDATEstatement to change this information:

/* This update fails! */
update titleview
set    pub_id = “0736”,
       au_ord = 1
where  title = “The Busy Executive’s Database Guide”
       and au_lname = “Green”
/* This update fails! */
update titleview
set    pub_id = “0736”,
       au_ord = 2
where  title = “The Busy Executive’s Database Guide”
       and au_lname = “Bennet”

Msg  4405,  Level  16,  State  2
View  ‘titleview’  is  not  updatable  because  the
FROM  clause
names  multiple  tables.

I tried to update au_ord, which lives in titleauthor, andpub_id, which lives in titles, in the same UPDATEstatement. The error message suggests that the view isn’tupdatable in any case, but that isn’t true. Here’s howto actually perform these updates:

/* First, update the pub_id. */
update titleview
set    pub_id = “0736”
where  title = “The Busy Executive’s Database Guide”
/* Now, change the au_ord for Green */
update titleview
set    au_ord = 1
where  title = “The Busy Executive’s Database Guide”
       and au_lname = “Green”
/* Last, change the au_ord for Bennet */
update titleview
set    au_ord = 2
where  title = “The Busy Executive’s Database Guide”
       and au_lname = “Bennet”

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Using the titleview view, I updated information in thetitles table with the first UPDATE statement. Althoughtitleview displays two rows for this title, there is really onlyone row in titles for this book. This is why I got a 1 row(s)affected message instead of two rows.

The author order is adjusted in a separate step. After all is said and done, the titles table has a book with a new publisher,and Green and Bennet have had their author orders reversed.

---

TIP:  Performance tip
When working with large tables, try to perform as many columnchanges as possible in a single UPDATE statement. If youchange two columns in one update, the row needs to be read andwritten only once. If you change one column in two UPDATEstatements, each row needs to be read twice and writtentwice.

---

Updating to Default Values

Starting with MS SQL Server v6.5 you can update a row in a tableand set the column back to its default value. The inventory table,created earlier today, has a default assigned to the qty column.Suppose these values exist in the inventory table:

inv_id      item_id     qty
----------- ----------- --
1           45          12
2           46          13

To update the last row and set the quantity back to its defaultvalue of 1, use this query:

update inventory
set qty  =  DEFAULT
where inv_id = 2

inv_id      item_id     qty
-------     --------    ---
1           45          12
2           46          1

Use the DEFAULT keyword to set a column to thedefault value. This is only usable when a default has been definedon the column.

---

TIP:  
If you want to set all rows to the default columnvalue, issue the UPDATE without restricting the rows onwhich it will operate.

---

Syntax Statement for UPDATE

Here is the syntax for the UPDATE statement:

update  <tablename>  |  <viewname>  |  <alias>
set     <column> =  <New   value>[,
        <column> =  <New   value>...]
[from   <table  list>]
[where  <boolean   constraint>]

DELETE

To remove rows from a table, use the DELETE statement.The DELETE removes a row from my inventory table:

delete   inventory
where  inv_id  =  1

(1 row(s) affected)

Just as with the UPDATE statement, be verycareful about qualifying the rows you want to affect with aWHERE clause. A DELETE without a WHEREclause deletes all the rows in a table!

Ensuring Successful Deletion

Watch the row counts as they come back from a DELETEoperation. Sometimes, you may delete no rows at all. If I tried todelete from the inventory table and specified an inv_id thatdidn’t exist, here is what I’d get back:

delete inventory
where inv_id = 100

(0 row(s) affected)

Issuing a DELETE that affects no rows does not cause an error. This is an especially important point when developing application programs. If you intend to delete a row, issue a DELETE, and find that you have deleted nothing, there is a very good chance there is a problem. Different programming libraries deal with this problem in different ways.

DELETE and Table Joins

The same rules that apply to table joins and updates apply to table joins and deletes. You may delete from only one table at a time, but you may join to other tables for information on which rows to delete. You must use non-ANSI join syntax, or a subquery, to perform a table join inside a DELETE.